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 **FUN**ctionality 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 character
**s**

Go back to the example on the top, it happens that the lookup values contain wildcard character “**?**” and “*****”.

The 1^{st} 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 2^{nd} 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.

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