VLOOKUP returns wrong value

[JWGoldfinch]

I have a VLOOKUP that is taking the value in a cell that has "*0026" and it is returning the value in the table for the value of "00026". In this case the two are not the same values there could be either or both in the lookup table. Any Ideas of how I can ensure that if "*0026" is not in the lookup table it will return the #N/A instead of the value for "00026"

 

[shyy]

the limitation to a VLOOKUP is that it only returns one instant of duplicate value. It takes the first it finds and keeps that as the answer. Check your data to see if the value that is being looked up has a dupe.

 

[dispelthemyth]

Make your lookup value like the below

"~"&A1


e.g. =Vlookup("~"&A1, B1:D5,3,0)

 

[JWGoldfinch]

Thank you guys, the "~"&$B2 work GREAT

 

[Scrappy666]

Sorry to hijack a thread but could you explain what the ~ does?
I've never seen that used before

 

[JWGoldfinch]

It's Ok to Hijack the thread, I don't know what it does either, but it returned the results that I was looking for.

 

[dispelthemyth]

Its a 'literal' search

Your original Vlookup query had you with *0026 as a lookup value, which is taken as a wildcard search due to the *
Now if you had a table with

Sheet1

*	A	B
1	90026	1
2	*0026	2

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

And you were searching for *0026 using a vlookup as =Vlookup("*0026", A1:B2,2,0) you would get a 1, as 90026 meets the criteria, that is *0026 is saying go look for anything that ends with 0026, it ignores the leading values, if you had *002*, it would search for 002 and ignore the leading and trailing characters


The Tilde (~) is used as a literal, now with the previous we were looking for a *0026, but excel did not see this so we had to tell it we really wanted it, so we use a Tilde (~), so if we had *0026 in C1, we would put ="~"&C1 which stops the * from being a wildcard so we are really search for *0026




Hope that helps

 

[JWGoldfinch]

Thank you for the explanation, makes total sense now why it works for me.

 

[Scrappy666]

Same, thank you!