Making VLOOKUP formulas go wild [VLOOKUP Week]

Posted on November 1st, 2010 in Learn Excel - 18 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:

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

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

18 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!

  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.

Leave a Reply