Why vlookup gives me wrong answer???
I guess many users are not aware of the wildcard characters in Excel. That’s why they miss a lot of FUNctionality in Excel.
Question Mark “?” and asterisk “*” are the wildcard characters in Excel, they can be used in Find/Replace, Search, Match, and lookup functions, just to name a few.
- ? means any single character
- * means any sequences of characters
Go back to the example on the top, it happens that the lookup values contain wildcard character “?” and “*”.
The 1st lookup value:
“Pant?8” is interpreted as a text string
- Starts with “Pant”;
- Ends with “8”;
- Any single character in between.
In the table array, “Pant28” is the first value matching that that lookup value, 300 is returned as a result.
The 2nd lookup value:
“Pant*29” is interpreted as a text string
- Starts with “Pant”;
- Ends with “29”;
- A sequence of any characters in between.
“Pant Blue 29” is thus the first value matching that lookup value, 200 is returned as a result.
Then how do we lookup a value that contains “?” or “*” in it?
Ummmmm…. Intuitively, people won’t remember to input the “~” for a lookup value. Is there a way to automate it?
Yes! We can do it by substituting the * and ? with ~* and ~? respectively by the following function:
In case we know that there will be either “?” or “*” in the source data table, we could modify the vlookup formula as follow:
See. We just need to modify the “lookup_value” inside the vlookup formula.
Here we go!
Description of the argument for SUBSTITUTE (from Excel Help)
The text or the reference to a cell containing text for which you want to substitute characters.
The text you want to replace.
The text you want to replace old_text with.
Specifies which occurrence of old_text you want to replace with new_text.
If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.
Other topics about vlookup:
- The basic of vlookup
- Tips in constructing vlookup
- vlookup with Match
- vlookup options – True or False?
- Advanced vlookup – Text vs. Number
- Advanced vlookup – Wildcard Characters “?” and “*”
- Alternative to vlookup – Index and Match
- Three different ways to do case-sensitive lookup