Advanced vlookup – wildcard characters “?” and “*”

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:



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:

  1. The basic of vlookup
  2. Tips in constructing vlookup
  3. vlookup with Match 
  4. vlookup options – True or False?
  5. Advanced vlookup – Text vs. Number
  6. Advanced vlookup – Wildcard Characters “?” and “*”
  7. Alternative to vlookup – Index and Match
  8. Three different ways to do case-sensitive lookup
This entry was posted in Formula and tagged , , . Bookmark the permalink.

27 Responses to Advanced vlookup – wildcard characters “?” and “*”

  1. Ciel says:

    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.


    • MF says:

      Hi Ciel,
      You may try the following:
      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.


  2. John says:

    SUMIFS(RANGE,CRITERIA,BEGINS WITH…… AND ENDS WITH…..??? I need to know how to combine the two formulae


    • MF says:

      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?


    • Igor says:

      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 %


      • MF says:

        Hi Igor,
        Thanks for your question.
        Unfortunately I don’t have an answer for you 😔


      • Igor says:

        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?


        • MF says:

          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. 😓


        • Igor says:

          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”


          • MF says:

            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:

            Note: the function SEARCH is not case-sensitive. It treats CHECKIN same as checkin.

            Hope this helps.


          • Igor says:

            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.


          • MF says:

            Hi Igor,
            I believe you just copy the formula and then paste it to Excel… Didn’t you?
            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.


          • Igor says:

            WORKS!!! THX MF!
            yes, I had to enter it manually, cant just copy and paste from webpage.


          • MF says:

            You are welcome.🙂


  3. vishal0589 says:

    Love it. Great Explanation


  4. Pingback: Excel lookup help

  5. Pingback: Vlookup with partial match

  6. Pingback: Alternative to vlookup – Index and Match | wmfexcel

  7. Pingback: Advanced vlookup – Text vs. Number | wmfexcel

  8. Pingback: vlookup – True or False?? | wmfexcel

  9. Pingback: vlookup with Match | wmfexcel

  10. Pingback: Tips in constructing vlookup | wmfexcel

  11. Pingback: The basic of vlookup | wmfexcel

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s