Vlookup help

Question

I am trying to create a lookup type worksheet where I use a drop down list to select a customer, then subsequently display specific information regarding that client in other fields.

Currently I have two worksheets. 1 - BDS (where I wish to display the information 2 - Add Info.

In the Add info worksheet I have various colums where I wish to input the data. Those colums are Customer Name, Relationship Manager, Address, etc.

On the BDS worksheet I have a drop down list for the customer name. I wish to display the Relation Ship Manager in a cell next to the name using VLOOKUP.

I have researched this for four hours now and am going mad. I think I need some absolute references, etc. but I am not certain what I need to do. I also think I need an IF statement.

I am NOT A PROGRAMMER, I am an intermediateuser and am trying to make an easy to use form for my collegues to access during tests.

Any help you can lend to me would be greatly appreciated.

Answer

I take it the customer name is the unique ID, so a simple vlookup will work based on this - the IF statement would be

if(isblank(YourInputCell),"",Vlookup(etc))

For vlookup it's going to be

vlookup(customerId,Table,column,false)

Absolute references won't matter if the formula is only in one cell

My email is aidan.heritage@virgin.net if you need further help with this- the email allows you to send sample files.

Question

I have a workbook with all the postcodes (zip codes) in a city (over 20,000) and for each postcode it gives a deprivation score of between 1 and 5.

I am analyzing a sample of data of around 5000 people and 1 of the fields is postcodes and need the deprivation score.

I tried using VLookup

I have tried the formula using the data from the original workbook.

=VLOOKUP(A2,'[Glasgow - Quintile.xls]Glasgow'!$A$2:$O$20296,15,FALSE)

I then copied and pasted the postcode and score onto the same workbook and tried

=VLOOKUP(A3,Sheet1!A2:B20296,2,FALSE)

Both have came up #N/A.

I have checked that the postcodes on both workbooks are the same format.

I'm at a loss of what to do as I don't want to spend hours flitting between the 2 workbooks to obtain the deprivation scores.

Answer

Jane,

both your formulas appear to be correct. So logically you should have success. It sounds like your postcodes contain all numbers.

in a situation such as that, the problem often is how the codes are stored.

sometimes numbers will be stored as text strings. So while the cell looks like it contains the number 123, it actually has the text string "123". functions like Vlookup and Match don't consider these to string to match each other "123" <> 123

You can test this by using the IsNumber or IsText formula

say your postal code is in A2. Somewhere put in the formula =isnumber(A2)

then do the same check on your database. If you don't get the same result, then that could be contributing to your problem.

If the value to be looked up is text and the database is number, then you could use

=vlookup(1*A2,Sheet1!$A$2:$B$20296,2,FALSE))

If the lookup is a number and the database is text you could do

=vlookup(text(A2,"@"),Sheet1!$A$2:$B$20296,2,FALSE))

the second could have problems with leading zeros.

Also, if your data came from the internet or from another source and was pasted into excel, you have to be concerned about entries having spaces in the code at the start or end. If from the internet, they could have a character with ascii code 160 which is a non-breaking space character.

If what I have told you doesn't help, you could sent me a single sample workbook with a couple of representative codes to look up in one sheet (also your formula) and also on another sheet, an extract from your database (say 10 or 20 rows that should match those codes) where the formula is failing and I could take a look.

send it to