Menu

# Making VLOOKUP formulas go wild [VLOOKUP Week]

Learn Excel - 20 comments

This article is part of our VLOOKUP Week. Read more.

### 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  Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

### Related articles:

 Written by Chandoo Tags: downloads, Learn Excel, Microsoft Excel Formulas, vlookup, vlookup week, wildcards in excel Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 20 Responses to “Making VLOOKUP formulas go wild [VLOOKUP Week]”

1. Ram Kapoor says:

Hi Chandoo,

To answer the first question we can use VLOOKUP("*ve*",\$B\$5:\$E\$17,2,FALSE)

2. Tom says:

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!

• Etienne says:

Hi Tom

Use this formula as a test if there are more than one values that comply with the lookup (G7 is the first cell of the dataRange and G25 is the last cell o the dataRange)

=ISNUMBER(MATCH(input,OFFSET(\$G\$7,MATCH(input,dataRange,0),0):\$G\$25,0))

The inner match function finds the position of the first result, the offset function adjusts the starting cell in the range to the cell below that and the second match function finds the position (if it exists) of the second positive result. If positive, it will be a number and the isnumber function will confirm that. You can then wrap this formula in a if formula eg. If( [above test] , "WARNING: More than one result, "")

3. dan l says:

Works well with a concatenate too......
=VLOOKUP(CONCATENATE(B10&"*"),A1:B6,2,FALSE)

4. Luke M says:

@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,)

5. Gregory says:

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.

6. [...] 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 [...]

7. gnana says:

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.

8. PM says:

Cannot figure out the Question 3 of the Homework problem, can someone please help.

thanks
PM

9. Alan says:

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

10. [...] 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 [...]

11. Abhi says:

not able to figure out the solution for 3 rd of downloaded file , can u pls healp me

12. CA.Hitesh Bansal says:

Please help me on 2nd QUestion in Downloaded file.

2. Who made more sales - person ending with ph or starting with je?

13. Matt L says:

Here are my solutions. Feedback appreciated!

1. 2277
=VLOOKUP("*"&G17&"*",B5:E17,3,FALSE)
2. Joseph
=INDEX(B5:B17,MATCH(MAX(VLOOKUP("*ph",B5:E17,3,FALSE),VLOOKUP("je*",B5:E17,3,FALSE)),D5:D17,0))
3. 726
=VLOOKUP(INDEX(B5:B17,MATCH(6,C5:C17,0)),B5:E17,3,FALSE)

14. Simone says:

Hello,

Just reading through this thread and I was wondering if anyone was able to help with a similar query. I am trying to use a wildcard on a string of numbers but when using the vlookups above it keeps returning N/A.

Below are my tables, I want to look up F1 in A1:A5 and return anthing that contains the 3 numbers.

This is what I have been trying to use:

=VLOOKUP("*"&F1&"*",A1:A5,1,FALSE)

Table 1
A1 Acc number Acc name
A2 2356161 Bob
A3 2165151 Sam
A4 4313621561 James
A5 46146143 Sarah

Table 2
Search code
F1 161
F2 143

Any help is much appreciated.

Thanks

15. nIl says:

1. 1. How many sales for the person whose name contains the text in G17
Ans: 2277
Formula: =IFERROR(VLOOKUP(CONCATENATE("*"&\$G\$17&"*"),\$B\$5:\$E\$17,3,FALSE),"Error")

2. 2. Who made more sales - person ending with ph or starting with je?
Ans: Joseph -
Formula: =IFERROR(IF(VLOOKUP("*ph",\$B\$5:\$E\$17,3,FALSE)>VLOOKUP("je*",\$B\$5:\$E\$17,3,FALSE), VLOOKUP("*ph",\$B\$5:\$E\$17,1,FALSE), VLOOKUP("je*",\$B\$5:\$E\$17,1,FALSE)),"Error")

3. 3. What is the netsales for the person who had 6 customers?
Ans: 726
Formula: =IFERROR(VLOOKUP(INDEX(\$B\$5:\$B\$17,MATCH(6,\$C\$5:\$C\$17,0)),\$B\$5:\$E\$17,3,FALSE),"Error")

Now, I'm confident about #1 and #2 above. I don't think #3 is correct. There are actually two people (Jessy and Jimmy) with 6 customers. However the forumla only finds Net Sales for Jimmy only (since VLOOKUP only returns value for the first match it finds). The real answer should be 1440 (adtrer adding 714 for Jimmy). Can someone share the correct formula?

Thanks in advance,

• Umesh says:

Dear Nil,

Sum ifs can be used "=SUMIFS(D5:D17,C5:C17,6)"

16. Connie says:

I need to use an if statement with vlookup and a wildcard, is this possible? it is looking at a cell to reference a table to get the terminal location.

=IF(\$A\$26="@ ATM Deposit 0492*",VLOOKUP("@ ATM Deposit 0492*",\$A\$2:\$B\$18,2,FALSE),IF(\$A\$26="@ ATM Deposit 0494*",VLOOKUP("@ ATM Deposit 0494*",\$A\$2:\$B\$18,2,FALSE)))

17. Asim says:

Very good post. I am using this trick with countif and sumif.

18. Etienne says:

Hi

I replied to a comment aboe befoe realizing the comment was from 2010, lol

Just for those concerned about the problem that there may be more than one result for the wildcard lookup (or also regular lookup) I think you can use this formula as a test if there are more than one values that comply with the lookup (in the example G7 is the first cell of the dataRange and G25 is the last cell of the dataRange - you could also replace these with index funtions eg. Index(dataRange,1,0) for the first and Index(dataRange,COUNTA(dataRange)+COUNTBLANK(dataRange),0) for the last )

Here is the test:

=ISNUMBER(MATCH(input,OFFSET(\$G\$7,MATCH(input,dataRange,0),0):\$G\$25,0))

The inner match function finds the position of the first result, the offset function adjusts the starting cell in the range to the cell below that and the second match function finds the position (if it exists) of the second positive result. If positive, it will be a number and the isnumber function will confirm that. You can then wrap this formula in a if formula eg. If( [above test] , "WARNING: More than one result, "")

 « What is VLOOKUP Formula & How to use it? Mix VLOOKUP with Data Validation for some magic! [VLOOKUP Week] »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.

#### Downloads

© 2019 All rights reserved