Google Sheets lookup with contains condition

Question

In the monthly statement of my bank account I want to tag every transfer with a category:

Description	Amount	Tag
Emma's veggie shop visa#1022	45	groceries
Zettle payment Bert automobile	345	car
Hugo discount visa#1023	99	groceries
Gpay: Emma's veggie shop	39	groceries

I made a table with all the keywords and corresponding tags:

Key	Tag
Emma's	groceries
Bert automobile	car
Hugo	groceries

Now I need a formula to fill out the Tag-column in the first table automatically, a bit like =ylookup(A2,Table2!A1:B3,2) where A2 is mapped to a row in Table2 that contains it's key word (not like vlookup matches) and returns the corresponding tag.

I'd use =vlookup(), but the transfer descriptions don't always begin the the same string. I've also looked at arrayformula, but didn't get anywhere.

Answer 1

Use filter() and search(), like this:

=single( iferror( filter(Table2!B$2:B, len(Table2!B$2:B), search(Table2!A$2:A, D2)) ) )

See filter() and iferror().

The single() function is one of the many undocumented functions in Google Sheets. It returns the first element of a 1D or 2D array.