fbpx
Search
Close this search box.

Making VLOOKUP formulas go wild [VLOOKUP Week]

Share

Facebook
Twitter
LinkedIn
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:

Data for this Example -Using Wildcards with VLOOKUP formula

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:

Data for this Example -Using Wildcards with VLOOKUP formula

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

VLOOKUP Week @ Chandoo.org - Learn tips on lookup formulas in Excel

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

21 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,

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

  19. Vinay says:

    My cell (A2) contains text separated by company details within cell like IBM;Wipro;Infosys; and I need a formula to display result as Yes if cell has companies other than IBM. If the cell has only IBM; IBM; IBM, result should show up as "No". Can you help please? I spent 4+ hours without luck 🙁

Leave a Reply