VLookup is not working correctly

[Eric @ BP-EVV]

I am looking up an on-hand quantity from one table to populate another table
based on an item number. I have a couple of scenarios where VLookup is
bringing back the same value for both items, but when I look them up
manually, I find that the two items have a different on-hand value. For item
number UX2022U and UX2022U* I get the same on-hand returned....smae thing
goes for item numbers UX2022R and UX2022R*. The vlookup I am using is this:

=VLOOKUP(A828,'[ilicst 16-jul-08.xls]Sheet1'!$A$2:$I$10189,8,FALSE)

Doesn't the "FALSE" in the expression tell Excel to find an EXACT match ? I
think the '*' in the last position of my item number is causing Excel to
treat that like a wildcard and not find the EXACT match.

Any ideas on how to work around this ?

Thanks !

 

[Ron Rosenfeld]

I am looking up an on-hand quantity from one table to populate another table
based on an item number. I have a couple of scenarios where VLookup is
bringing back the same value for both items, but when I look them up
manually, I find that the two items have a different on-hand value. For item
number UX2022U and UX2022U* I get the same on-hand returned....smae thing
goes for item numbers UX2022R and UX2022R*. The vlookup I am using is this:

=VLOOKUP(A828,'[ilicst 16-jul-08.xls]Sheet1'!$A$2:$I$10189,8,FALSE)

Doesn't the "FALSE" in the expression tell Excel to find an EXACT match ? I
think the '*' in the last position of my item number is causing Excel to
treat that like a wildcard and not find the EXACT match.

Any ideas on how to work around this ?

Thanks !

That is very strange. I've never run across it, but I can confirm it occurs in
Excel 2007. And it also occurs with the MATCH worksheet function. It doesn't
seem to be documented in HELP.

This **array-entered** formula seems to be one work-around:

=INDEX('[ilicst 16-jul-08.xls]Sheet1'!$A$2:$A$10189,MATCH(
TRUE,EXACT(A10,'[ilicst 16-jul-08.xls]Sheet1'!$H$2:$H$10189),0))

To **array-enter** a formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.
--ron

 

[T. Valko]

I think the '*' in the last position of my item number is

causing Excel to treat that like a wildcard and not find
the EXACT match.

You are correct.

Try it like this:

=VLOOKUP(SUBSTITUTE(A828,"*","~*"),'[ilicst
16-jul-08.xls]Sheet1'!$A$2:$I$10189,8,0)

In the formula, we "virtually" replace the * with ~*. The ~ tells Excel to
treat the * as a literal character and not a wildcard.

Note that this is based on your *limited* sample. The sample lookup values
you posted are TEXT strings. This approach won't work if your lookup values
were numbers because the result of the SUBSTITUTE function is a *TEXT*
value. For example:

10
10*

The above formula wouldn't be able to find 10 because the SUBSTITUTE
function converts it from numeric 10 to text "10".



--
Biff
Microsoft Excel MVP

I am looking up an on-hand quantity from one table to populate another
table
based on an item number. I have a couple of scenarios where VLookup is
bringing back the same value for both items, but when I look them up
manually, I find that the two items have a different on-hand value. For
item
number UX2022U and UX2022U* I get the same on-hand returned....smae thing
goes for item numbers UX2022R and UX2022R*. The vlookup I am using is
this:

=VLOOKUP(A828,'[ilicst 16-jul-08.xls]Sheet1'!$A$2:$I$10189,8,FALSE)

Doesn't the "FALSE" in the expression tell Excel to find an EXACT match ?
I
think the '*' in the last position of my item number is causing Excel to
treat that like a wildcard and not find the EXACT match.

Any ideas on how to work around this ?

Thanks !

 

[Dave Peterson]

Just to add...

My favorite!

=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~","~~"),"?","~?"),"*","~*"),
Sheet2!$A:$B,2,FALSE)

To handle both the * and ? wildcard and the ~ "escape" character.

I am looking up an on-hand quantity from one table to populate another table
based on an item number. I have a couple of scenarios where VLookup is
bringing back the same value for both items, but when I look them up
manually, I find that the two items have a different on-hand value. For item
number UX2022U and UX2022U* I get the same on-hand returned....smae thing
goes for item numbers UX2022R and UX2022R*. The vlookup I am using is this:

=VLOOKUP(A828,'[ilicst 16-jul-08.xls]Sheet1'!$A$2:$I$10189,8,FALSE)

Doesn't the "FALSE" in the expression tell Excel to find an EXACT match ? I
think the '*' in the last position of my item number is causing Excel to
treat that like a wildcard and not find the EXACT match.

Any ideas on how to work around this ?

Thanks !