VLOOKUP with WILDCARD characters

//VLOOKUP with WILDCARD characters
excel-vlookup-wildcard
Advanced Vlookup with  – 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!!!

2

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 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:3
    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)

4

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.

Turtortek is founded by IT professionals with more then 20 years experience and having done management certification from IIM Kolkata. We are focused on delivering project based quality IT trainings in Cloud Computing, Big Data & Hadoop, Analytics, Testing, Web Technologies and Android where the emphasis is mainly on real-time project based learning. We provide instructor led class-room, online training and self-learning video tutorials.

No comments yet.

Leave a comment

Your email address will not be published.