Vlookup Problem

[Guest]

I have a spreadsheet which uses a VLOOKUP of approximately 30,000 item
numbers on a different tab called Product Lookup, to provide description,
price and 4 other pieces of information.

For some reason, some of my item numbers cannot be found even though they
are on the Product Lookup and all my item numbers are sorted in ascending
order.

The only way I can get the information I'm looking for to show up is if
manually go to the Product Lookup tab and copy the Item number I want and
paste it in the other tab.

Here is the the code I'm using

=IF(B10="","",VLOOKUP(B10,ProductLookup,2,FALSE))

Any ideas on why this might be happening?

 

[Pete_UK]

It might be that you have a number in B10 and text values that look
like numbers in the first column of your table (or vice-versa).

If you are dealing with text values, like product codes, it might be
that you have leading or trailing spaces in B10 or in your table - you
can use TRIM to get rid of these.

Hope this helps.

Pete

 

[Guest]

Thanks, but I'm still a novice, so I'm not exactly sure what you mean.

In B10 we enter an item number and on the vlookup we look for the item
number in B10 in the first column in my vlookup and the 2nd column is the
description.

The order codes can be all numeric, alpha-numberic, or even have special
characters like - and = along with numbers and alpha.

Thoughts?

 

[Guest]

Oops, I did take all my order codes and TRIM them and then did a paste
special - values back in to my worksheet and it seems to be working.

I apolgize for not trying that first.

Thanks so much@

 

[Guest]

Try this, if it shows up as " ", then you know it's a format issue

=IF(ISERROR(VLOOKUP(B10,ProductLookup,2,FALSE)),"
",VLOOKUP(B10,ProductLookup,2,FALSE))

Try copying the lookup numbers and pasting them as values then seeing if
their is a little question mark or some little box that you can click and
convert them from text to numbers.

 

[Pete_UK]

Thanks for feeding back - glad it worked for you.

When you use VLOOKUP with FALSE as the 4th parameter you are looking
for an EXACT match, so leading and/or trailing spaces can throw this
out. You might also like to put TRIM(B10) instead of just B10 in your
VLOOKUP formula to get rid of any unwanted spaces in the input.

Hope this helps.

Pete

 

[Gord Dibben]

In addition to Pete's advice......you don't need the list to be sorted when
using the 4th parameter of FALSE as you have done.

If an exact match is not found you will get #N/A


Gord Dibben MS Excel MVP