SubscribeMaking VLOOKUP formulas go wild [VLOOKUP Week]
Situation
Often we need our lookup formulas to go wild. Not in the sense of go-wild-and-chomp-a-few-kilo-bytes-of-data sense. But wild like wild cards.
For eg. In the below data, we may not remember the full name of sales person, but we know that her name starts with jac. Now how do you get the sales amount for that person?
Data:
Solution
Simple. Use wild cards. Like this: =VLOOKUP("jac*",$B$5:$E$17,3,FALSE) to fetch the value from 3rd column for the person whose name starts with jac
Examples:
Sample File
Download Example File – Using Wild cards with VLOOKUP formula
Special Thanks to
Michael Pennington, Lukas for the tip. (Click on the name to see their tip)
Similar Tips
- Excel SUMIF and COUNTIF Formulas – What are they, how to use & examples
- Search Workbooks using Conditional Formatting & Wildcards
| ||
|
Spread some love,
Posts & Navigation
Tags: downloads, Learn Excel, Microsoft Excel Formulas, vlookup, vlookup week, wildcards in excel |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
10 Responses to “Making VLOOKUP formulas go wild [VLOOKUP Week]”
Hi Chandoo,
To answer the first question we can use VLOOKUP(“*ve*”,$B$5:$E$17,2,FALSE)
This is a great tip, and one I can see applying to other formulas as well. My only concern: what if your wildcard has more than one answer? Vlookup can only handle one response, correct? And it will only return one value (i’m guessing the first record that it encounters where it meets that condition). This could really mess you up if you just assumed it was right – so be careful with Vlookup and wildcards!
Works well with a concatenate too……
=VLOOKUP(CONCATENATE(B10&”*”),A1:B6,2,FALSE)
@dan I
CONCATENATE function is a waste of characters (and since we all hate having to work harder than we have to), especially since you took the shorter (and faster) method of concatenating the words yourself. A shorter, but exact same re-written function would be:
=VLOOKUP(B10&”*”,A1:B6,2,)
If you use the formula =VLOOKUP(“ja*”,B5:E17,3,FALSE) there are 5 possible answers, but the formula returns 2133, which is the first one encountered. Hence using a wildcard with the VLOOKUP formula isn’t something I’ve used in the past, mainly because with a large data set you can’t be sure of the result.
[...] at Chandoo's Excel blog, he's celebrating VLOOKUP week, with helpful posts like VLOOKUP Formulas Go Wild. Who knew an Excel formula could go wild? I've seen many workbooks where things have run amok, but [...]
How to make “Jo*” as vlookup value as a range.
=VLOOKUP(“Jo*”,A1:M14,2,FALSE)
The above one will vlookup only for the “Sales Person” contains letter “Jo*”. If i want to vlookup value for “Je*”, “Ae” & “Co*”. How i can do.
Cannot figure out the Question 3 of the Homework problem, can someone please help.
thanks
PM
PM,
I copied and pasted the number of customers (column ‘D’) to the right of the Name column, and then did a normal vlookup formula.
Best,
Alan
[...] I manage to learn a new trick. Just recently I found out that you can use wildcards in vlookup formulas [^]. Wildcards are “*” and “?”, where ? replace any one character, and * replaces any [...]