VLOOKUP where part of lookup-cell can be contained in lookup table

Question

Most of the time, I use wildcards in VLOOKUP to get a match when my lookup table contains longer descriptions of which a part might match my lookup value.

I now have the opposite usecase; I have a table containing bank transactions, and want to add a column to that table with what category the transaction falls in. For common payments, I created a second table with general keywords linking to categories, so I don't have to fill them in manually. As such, my main table - in the last column of which my vlookup will find place - looks as follows:

And my lookup table might look as follows:

How can I now structure my VLOOKUP in the "Category" will find the appropriate value in the lookup-table based on just part of the string it's looking up?

i.e., if my main table contains the value "abc KEYWORD def" and my lookup table contains the key "KEYWORD", I want the lookup call to match it with the value it finds for "KEYWORD".

I tried to use the whole wildcard thing somewhat like this =VLOOKUP("*"&LookupCell&"*", LookupTable, 2, FALSE), but afaik that works the exact opposite way of what I want to do, by checking if my lookup table might contain a key looking like "otherstuff abc KEYWORD def otherstuff".

Answer 1

For Excel 2019 please see Edit 1 below

See if this works for you. I have not extensively tested it.

See the attached screenshots to get an idea how the sample data is organized.

The formula in E3 is

=TEXTJOIN("",TRUE,IFERROR(VLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(C3," ","</s><s>")&"</s></t>","//s"),$C$12:$D$16,2,FALSE),""))

Select and Drag it down up to the intended rows.

This is tested on Excel 2021, which by default automatically creates an Array Formula. But in 2019 you might need to Press CTRL + SHIFT + ENTER from within the formula bar to create an Array Formula.

The array formula will be automatically enclosed in curly braces.

Please check if this works for you.

I guess the catch is if there are more than one matches the formula could misbehave or show all entries in one line!

---

Edit 1 -

The above solution will only work in Excel 2021 or may be O365 but if you have Excel 2019 the Array Formula as-is won't work. Checked on Excel 2019.

Here you need a slightly different solution.

See the below screenshot for sample data and formula.

Formula in E3 is

=TEXTJOIN("",TRUE,IFERROR(INDEX($D$12:$D$16,N(IF(1,MATCH((TRANSPOSE((FILTERXML("<t><s>"&SUBSTITUTE(C3," ","</s><s>")&"</s></t>","//s")))),$C$12:$C$16,0)))),""))

Press CTRL + SHIFT + ENTER from within the formula bar to create an Array Formula. The formula will automatically enclose in curly braces to indicate that it's an array formula.

Now drag it down up to the intended rows.

Answer 2

Excel oofers many options to accomplish what you are looking for. My preferences are a combination of INDEX and MATCH functions or XLOOKUP function. Adapt the following formulae.

1. INDEX and MATCH functions or XLOOKUP functions:

=INDEX($B$12:$B$18,MATCH("*"&C2&"*",$A$12:$A$18,0))

2. INDEX and MATCH functions or XLOOKUP functions:

=XLOOKUP(C2,$A$12:$A$18,$B$12:$B$18,,0)

Use IFNA in case you don't want to see #NA error. OR insert a text message in the XLOOKUP formula to display in its place.

Demo results

Answer 3

Here's another way using this formula: =LET(findArray,ISNUMBER(FIND($A$11:$A$16,UPPER(A2))),VLOOKUP(TRUE,HSTACK(findArray,$B$11:$B$16),2,FALSE))

LET - allows naming of calculations for further use

findArray - the name of my variable

ISNUMBER(FIND($A$11:$A$16,UPPER(A2))) - creates a true/false array using ISNUMBER based on matches with FIND. UPPER is used because of case sensitivity.

VLOOKUP(TRUE,HSTACK(findArray,$B$11:$B$16),2,FALSE) - typical VLOOKUP looking for TRUE in a rebuilt table using HSTACK.

TWO CAVEATS: 1) common category must have unique values. 2) if supplier name is such that it could belong to two common categories (another supplier had the name Miles seDMan). Depending on how you want to deal with this using filter and textjoin could be substituted for vlookup.