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.

  • @BlindSpots Table2!A1:B3 is a valid range reference in Google Sheets. Feb 7 at 7:40
  • is Table2 a sheet name or named range?
    – Blind Spots
    Feb 7 at 18:03
  • Table1 & Table2 are sheets, yes. Should have called them sheet1 & sheet2.
    – simaglei
    Feb 7 at 18:33

1 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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.