vlookup() error #n/a

F

Frank Pytel

Hello;

I am doing a simple function. I can't believe I have been trying to get this
to work for two hours. I know it's probably something stupid, so here goes.

=VLOOKUP(C3,ProductList,10,FALSE)

This formula is returning #n/a.

ProductList is a named range on sheet 1. It refers to cells 'Product
List!'$a$2:$j$1000. I had renamed the sheet 1 to "Product List". I renamed
the worksheet again, hoping it was a bug, to the letter a. No luck.

C3 is my qualifier. I have to qualify based on two criteria. IE all "Black"
and "10 oz" or "Pepsi" and "1 liter". I have come up with a workaround for
the vlookup by using a helper cell and concatenating the values. C3 is
concatenated from A3:B3 and the helper cell in my table concatenates the
same, relative, information from the table. So I am searching the same value.
Can vlookup lookup the values of cells that occur during concatenation? I
just tried switching from the concatenated cells to static reference cells
and still get the #n/a error.

10 is the column I am looking for the information for
False, Exact reference.

I have tried naming the table cells directly, naming fewer cells, deleting
the named range and selecting the table. Nothings working. Maybe fresh eyes
in the morning:)

Help, Please

God Bless

Frank Pytel
 
T

T. Valko

#N/A means there is no match between the lookup_value and the leftmost
column of the table_array. Concatenation will work as long as the leftmost
column of the table_array contains the concatenated lookup values. You can
concatenate the multiple lookup_values directly in the formula or use a
helper cell.

Screencap:

http://img524.imageshack.us/img524/9733/samplewe7.jpg


It's probably something simple like leading/trailing spaces
 
F

Frank Pytel

Worked like a charm. Thanks Biff. I was trying to find the data in the 4th
column. I gotta go to bed earlier.

God Bless

Frank Pytel
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top