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

#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

--
Biff
Microsoft Excel MVP


"Frank Pytel" wrote in message
...
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

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" wrote:

#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

--
Biff
Microsoft Excel MVP


"Frank Pytel" wrote in message
...
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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Frank Pytel" wrote in message
...
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" wrote:

#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

--
Biff
Microsoft Excel MVP


"Frank Pytel" wrote in message
...
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