Search code examples
excelexcel-formulavlookup

Using Excel for VLOOKUP and WildCard Not Working

Can you please help me discover why VLOOKUP with Wildcard is not working. I have googled for hours and tried everything.

Using:

=VLOOKUP("*"& E2 &"*",$H$2:$I$3,2,0)

It does work when E2 (the look up cell) is an exact match to the keyword, but not otherwise.

Excel Example

excel example

Sample:

Date Tags SubCat Vendor Description Amount
3/4/2019 Food Groceries Hannaford Market TST* HANNAFORD DBT CRD 0000 03/03/19 22167640 C#5055  $170.00
3/4/2019 Food Dinning Out Not Found TST* PRICE CHOP DBT CRD 0000 03/03/19 22167640 #5055  $ 40.09
3/4/2019 Food Dinning Out Pizzeria Downtown PIZZERIA DO DBT CRD 0000 03/02/19 161146526 C#5055  $ 86.32
Keywords Vendor
Hannaford Hannaford Market
pizzeria Pizzeria Downtown
Solution

  • Instead of using VLOOKUP() its better to use XLOOKUP() function in your context of searching the relevant keywords and return the corresponding vendors:

    enter image description here

    =XLOOKUP(1,1-ISERR(SEARCH($H$2:$H$3,E2)),I$2:I$3,"Not Found")

    And if you intent to use VLOOKUP() only then try using the following:

    enter image description here

    =IFERROR(TOCOL(VLOOKUP(TEXTSPLIT(E2," "),$H$2:$I$3,2,0),2),"Not Found")

    Or, Using LOOKUP() function :

    =IFERROR(LOOKUP(2,1/(SEARCH($H$2:$H$3,E2)),$I$2:$I$3),"Not Found")

    Or Could use the following, but it will show blank if nothing is found:

    =TEXTJOIN("",1,IF(ISNUMBER(SEARCH($H$2:$H$3,E2)),$I$2:$I$3,""))

    Note that I have used 1-ISERR() which is equivalent to ISNUMBER(), you can use ISNUMBER() in place of the former as well!

    One more thing it is better to add boundaries before and after the keyword:

    =XLOOKUP(TRUE,ISNUMBER(SEARCH(" "&$H$2:$H$3&" "," "&E2&" ")),I$2:I$3,"Not Found")

    XLOOKUP() function doesn't needs the other functions when there is no match to return a Not Found !