Why vlookup gives me wrong answer???
Bugs in vlookup??? I am sure it’s not about text vs. number, but it still fails to give the correct 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.
Wildcard Characters:
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?
To lookup exactly the “?” or “*”, we need to add “~” just before it. I.e. “~?”, “~*”. See below:
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:
SUBSTITUTE(text,old_text,new_text,instance_num)
In case we know that there will be either “?” or “*” in the source data table, we could modify the vlookup formula as follow:
=VLOOKUP(SUBSTITUTE(lookup_value,"*","~*"),$A$3:$B$8,2,FALSE)
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)
SUBSTITUTE(text,old_text,new_text,instance_num)
text
The text or the reference to a cell containing text for which you want to substitute characters.
old_text
The text you want to replace.
new_text
The text you want to replace old_text with.
Instance_num
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.
wmfexcel 
Hi Fung, i have a speadsheet with a long list of brand names in full (eg: life is good) and another speadsheet with list of brand names in partial (eg: is good). Is it possible to run a formula that list the brand names in full based on the partial brand names? Please help.
LikeLike
Hi Ciel,
You may try the following:
=VLOOKUP(““&C1&”“,$A$1:$A$10,1,0)
where A1:A10 is the list of names in full; C1 is the names in partial
However, it really depends on how your brand names are…
If you have “Food is good” before “Life is good”, then the formula will give you “Food is good”.
Hope it helps.
LikeLike
ooops, the text editor ate the asterisk in between the double quotation…
LikeLike
SUMIFS(RANGE,CRITERIA,BEGINS WITH…… AND ENDS WITH…..??? I need to know how to combine the two formulae
LikeLike
Hi John,
Pls note that the syntax of SUMIFS is a bit different from that of SUMIF.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
The following formula would give the the result that SUM from A1:A20 (SUMRANGE) that is larger than or equal 0 and less than or equal 10
=SUMIFS(A1:A20, A1:A20, “>=0”, C1:C20, “<=10”)
Is it what you are looking for?
LikeLike
Thank you so much for explanation.
I just don’t understand why the symbols in excel are different from other programming languages like SQL for example? in SQL wildcard character is %
LikeLike
Hi Igor,
Thanks for your question.
Unfortunately I don’t have an answer for you 😔
LikeLike
Thanks John anyway, good job on this topic. I love Excel.
LikeLike
You are welcome. Btw my name is Fung. You may also call me MF. You may read my About page should you want to know me more.
Have a nice weekend!
Cheers, MF 😁
LikeLike
I mean there should be some unique number of characters which could be used in each programming language. For example when you built Microsoft excel formulas why can’t you use the same statements or keywords as in SQL?
LikeLike
Yes you are right I guess. However i have no knowledge in SQL at all. That’s why I am not able to answer your question. 😓
LikeLike
Hi Fung, it’s me again,
I have a question for you, here is a formula we have on the shared spreadsheet:
=IF(OR(J25=”Page – Check In”),”CT”,IF(OR(J25=”Sunday – Page – Check In”),”CT”,”R”))
now, I want to modify it, because every time you want to add something with Check In at the end the formula won’t catch it unless you add it to it. I thought I can use wildcard for this task, but not sure how. This =IF(J2=”*Check In”,”CT”,”R”) doesn’t work. Any idea?
In SQL it’s easy: SELECT * FROM (TABLE) WHERE (COLUMN) LIKE “%Check in”
LikeLike
Hi Igor,
Thanks for your question.
First of you, I think you don’t need OR in your formula. There is only one logical test being put for OR, so you don’t need it to produce the result you need.
Second, not all functions in Excel take wildcards. J2=”*CheckIn” is one of the examples. 🙂
Actually you don’t need vlookup nor wildcards for your problem. Try the following:
=IF(ISNUMBER(SEARCH(“checkin”,J25)),”CT”,”R”)
Note: the function SEARCH is not case-sensitive. It treats CHECKIN same as checkin.
Hope this helps.
Cheers,
LikeLike
Hey Fung, unfortunately your formula doesn’t work: (I’d like to insert image here, but I can’t) it gives me #NAME?
After evaluation it shows this: =IF(ISNUMBER(SEARCH(“#NAME?”,A2)),”CT”,”R”), for some reason formula does not recognizing “Check in” text.
LikeLike
Hi Igor,
I believe you just copy the formula and then paste it to Excel… Didn’t you?
=IF(ISNUMBER(SEARCH(“checkin”,J25)),”CT”,”R”)
Please pay attention to the pasted formula in the formula bar. Do you notice that the double quotation mark is somewhat different?
Please replace all the double quotation mark in the formula bar. You have to input it manually in the formula bar in Excel.
Pls try and revert.
Cheers,
LikeLike
WORKS!!! THX MF!
yes, I had to enter it manually, cant just copy and paste from webpage.
LikeLike
You are welcome. 🙂
LikeLike
Love it. Great Explanation
LikeLike
Hi vishal
Thanks for your kind words!
Cheers,
LikeLike
Pingback: Excel lookup help
Pingback: Vlookup with partial match
Pingback: Alternative to vlookup – Index and Match | wmfexcel
Pingback: Advanced vlookup – Text vs. Number | wmfexcel
Pingback: vlookup – True or False?? | wmfexcel
Pingback: vlookup with Match | wmfexcel
Pingback: Tips in constructing vlookup | wmfexcel
Pingback: The basic of vlookup | wmfexcel