Excel - Look for partial matches in a cell and return the matched category from another table

Question

I have a table (Table1) with "Keywords" down one column, and their corresponding "Category" in the next column, for example:

Keyword Category
 EE     Telecommunications
 Water  Utilities
Amazon  Internet Transaction

On a separate worksheet, I have another table (Table2) of transactions. I want to check if any of the Keywords from Table1 are in the description of each transaction in Table2, and if so, return the corresponding Category (from Table1). If multiple matches are found, I just need a value returned to indicate so (so either the name of the only category matched, blank for no matches found, or another value (e.g 'Multiple') if more than one matches were found).

I have tried INDEX/MATCH and SEARCH combinations, and I've been researching for hours. I'm pretty sure I need to use an {array} formula, but I just cannot get it right.

I've also tried VBA to run through each row in Table2 to check against each row in Table1 which does work, but takes a very, very long time with lots of transactions.

Table2 needs to look something like this:

Description    Category
PAYPAL PAYMENT Internet Transaction
AVIVA          Insurance
BD270 MKN      
Anglian Water  Utilties
ATM 32334      <multiple matches>

More information, please! With your example, "Water" appears in both tables. So it should return "Utilities", next to table 2? or should it return "Anglian Water" next to table 1? — ashleedawg
A properly written vba will be much quicker than the array formulas you will need to do this with formulas. Post the vba you got to work on codereview.stackexchange.com with the explanation and they will help optimize the code to work quicker. — Scott Craner

Fernando J. Rivera Fernando J. Rivera · Accepted Answer · 2017-12-15T18:14:24

This will do what you want, though I don't promise it will be very efficient with several records. Enter it as an array formula with Ctrl+Shift+Enter

{=IF(SUM(--NOT(ISERROR(FIND([table1.Col1],[table2.lookupValue]))))<>1,IF(SUM(--NOT(ISERROR([table1.Col1],[table2.lookupValue]))))>1,"Multiple matches",""),INDEX([table1.Col2],SUM((ROW([table1.Col1])-1)*NOT(ISERROR(FIND([table1.Col1],[table2.lookupValue]))))))}

Excel - Look for partial matches in a cell and return the matched category from another table

1 Answers