vlookup() error #n/a

[Guest]

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. 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

 

[Guest]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!