VLookup problem
 

Hi All,

I'm trying to fill in a retail price based on the product code provided in a
neighboring column. That product code is being pulled via a lookup function
and works fine. The price on the other hand gives me the dreaded #N/A, even
though I can copy the product code and do an alt-F in the table array to find
the exact match.

I added the text() function within my VLOOKUP, and verified that it's
searching against text. If I change the range_lookup to TRUE, it uses the
code directly above the correct one in the table array and gives me the price
for that one instead.

I'm tearing my hair out because I can't figure out what's wrong with it!
Please help!

Is the data imported, especially from a website?
Regards,
Alan
"K" wrote in message
...
Hi All,

I'm trying to fill in a retail price based on the product code provided in
a
neighboring column. That product code is being pulled via a lookup
function
and works fine. The price on the other hand gives me the dreaded #N/A,
even
though I can copy the product code and do an alt-F in the table array to
find
the exact match.

I added the text() function within my VLOOKUP, and verified that it's
searching against text. If I change the range_lookup to TRUE, it uses the
code directly above the correct one in the table array and gives me the
price
for that one instead.

I'm tearing my hair out because I can't figure out what's wrong with it!
Please help!

I would also try the trim function to insure there are no spaces.
A way to check it also is to look at two cells
one what gets entered in the lookup value cell (A1)
and the other one that looks like that value that you would expect to be
selected by the Vlookup (A40)
in a blank cell enter
=A1=A40
if the answer is false there could be several things
try
=len(A1)-Len(A40)
=isnumber(A1)
=isnumber(A40)
because something does not match

"K" wrote:

Hi All,

I'm trying to fill in a retail price based on the product code provided in a
neighboring column. That product code is being pulled via a lookup function
and works fine. The price on the other hand gives me the dreaded #N/A, even
though I can copy the product code and do an alt-F in the table array to find
the exact match.

I added the text() function within my VLOOKUP, and verified that it's
searching against text. If I change the range_lookup to TRUE, it uses the
code directly above the correct one in the table array and gives me the price
for that one instead.

I'm tearing my hair out because I can't figure out what's wrong with it!
Please help!

Yup - imported from a billing system.

"Alan" wrote:

Is the data imported, especially from a website?
Regards,
Alan
"K" wrote in message
...
Hi All,

I'm trying to fill in a retail price based on the product code provided in
a
neighboring column. That product code is being pulled via a lookup
function
and works fine. The price on the other hand gives me the dreaded #N/A,
even
though I can copy the product code and do an alt-F in the table array to
find
the exact match.

I added the text() function within my VLOOKUP, and verified that it's
searching against text. If I change the range_lookup to TRUE, it uses the
code directly above the correct one in the table array and gives me the
price
for that one instead.

I'm tearing my hair out because I can't figure out what's wrong with it!
Please help!

K wrote:
Hi All,

I'm trying to fill in a retail price based on the product code provided in a
neighboring column. That product code is being pulled via a lookup function
and works fine. The price on the other hand gives me the dreaded #N/A, even
though I can copy the product code and do an alt-F in the table array to find
the exact match.

I added the text() function within my VLOOKUP, and verified that it's
searching against text. If I change the range_lookup to TRUE, it uses the
code directly above the correct one in the table array and gives me the price
for that one instead.

I'm tearing my hair out because I can't figure out what's wrong with it!
Please help!
Post your formula.

Alan Beban

Good call, the data in the lookup value cell is one space shorter than the
data in the array. How do I figure out where that space is and remove it?

"bj" wrote:

I would also try the trim function to insure there are no spaces.
A way to check it also is to look at two cells
one what gets entered in the lookup value cell (A1)
and the other one that looks like that value that you would expect to be
selected by the Vlookup (A40)
in a blank cell enter
=A1=A40
if the answer is false there could be several things
try
=len(A1)-Len(A40)
=isnumber(A1)
=isnumber(A40)
because something does not match

"K" wrote:

Hi All,

I'm trying to fill in a retail price based on the product code provided in a
neighboring column. That product code is being pulled via a lookup function
and works fine. The price on the other hand gives me the dreaded #N/A, even
though I can copy the product code and do an alt-F in the table array to find
the exact match.

I added the text() function within my VLOOKUP, and verified that it's
searching against text. If I change the range_lookup to TRUE, it uses the
code directly above the correct one in the table array and gives me the price
for that one instead.

I'm tearing my hair out because I can't figure out what's wrong with it!
Please help!

IT WORKED! I used the trim function and it solved my problem! Thanks so
much - it's been 2 wasted days on this!

"bj" wrote:

I would also try the trim function to insure there are no spaces.
A way to check it also is to look at two cells
one what gets entered in the lookup value cell (A1)
and the other one that looks like that value that you would expect to be
selected by the Vlookup (A40)
in a blank cell enter
=A1=A40
if the answer is false there could be several things
try
=len(A1)-Len(A40)
=isnumber(A1)
=isnumber(A40)
because something does not match

"K" wrote:

Hi All,

I'm trying to fill in a retail price based on the product code provided in a
neighboring column. That product code is being pulled via a lookup function
and works fine. The price on the other hand gives me the dreaded #N/A, even
though I can copy the product code and do an alt-F in the table array to find
the exact match.

I added the text() function within my VLOOKUP, and verified that it's
searching against text. If I change the range_lookup to TRUE, it uses the
code directly above the correct one in the table array and gives me the price
for that one instead.

I'm tearing my hair out because I can't figure out what's wrong with it!
Please help!