Cant get VLOOKUP to work

**paulboy** · 04-19-2009, 07:18 AM

Hi,
I am trying to get VLOOKUP to function but am having a lot of trouble at the moment. I did a trial on a practice workbook but it is not working on the real thing. This is what I am trying to achieve:

My workbook has 20 worksheets. The first 19 have two columns each. Column A has product descriptions and Column B has product codes. Within these 19 sheets there are gaps in Column A where there is no product description typed but Column B has all the product codes present.

Sheet 20 is a master list of product descriptions in Column A and product codes in Column B. I want to match the product codes in sheets 1 to 19 with the product codes in the master list and have the product description copied to Column A in sheets 1 to 19. Sounds straightforward but for some reason it is not working. This is what I am doing:

On sheets 1 to 19 my product codes all start in cell B11. I have called the worksheet that has the master list "Data" so this is what I have typed -

=VLOOKUP(B11,Data!A2:B715,1,FALSE)

I keep getting the same result which is #N/A. I am by no means an expert with excel so it is entirely possible that I have missed something obvious or done something stupid . Any suggestions and help would be gratefully received.
Thanks,
Paul.

**mrice** · 04-19-2009, 07:44 AM

If I've understood your description correctly, it sounds like you need to reverse the order of your columns in the master sheet. VLOOKUP looks at the left hand column in the rangespecified in the second argument and returns the value on the desired column to the right.

**stanleydgromjr** · 04-19-2009, 08:39 AM

PAULBOY,

In Sheet1, cell A11, try:
=IF(ISNA(INDEX(Data!A:A,MATCH(B11,Data!B:B,0),0)),"Not Found",INDEX(Data!A:A,MATCH(B11,Data!B:B,0),0))

Then copy cell A11 down.

If this does not work, please post your workbook - scroll down and see "Manage Attachments".

Have a great day,
Stan

**paulboy** · 04-19-2009, 12:36 PM

Hi Stan,
Thanks for your reply. I used your code and it worked perfectly so many thanks for that. I have no idea how you worked it out but I know that I could not have figured this out. I really do have a lot to learn it would seem.
All the best,
Paul.