google-sheetsgoogle-sheets-formulaarray-formulasauto-populatekeyword-search

Keyword Search with Auto Populate Array Formula


I have a list of bank transaction that each transaction has to be categorized. I have formulas that populate C2:C which works as keyword searcher.

A2:A are the bank transaction descriptions
C2:C are the bank transaction category
E2:E are the keyword
F2:F are the category for the keyword

So, the formula searches for keyword in description A2:A that contains keywords in E2:E and populates C2:C with the return of the corresponding category with the keyword that matches with F2:F.

The problem is, I have to drag down the formula each time I add a new bank description. Is there any array formula that works like keyword searcher like that but may also auto populate the cell below if I add a new bank transaction?

Here is the case:

https://docs.google.com/spreadsheets/d/1LlCSGgQaUEwIDMgEG_G9YXQGpnn99IIp9C7OGkFXYkY/edit?usp=sharing

I tried to do a little research before this, but somehow didn't find any good one, or maybe I wasn't lucky enough to find one. If anyone can help, that would be very good.


Solution

  • try:

    ={"category"; ARRAYFORMULA(IFNA(VLOOKUP(IFNA(REGEXEXTRACT(LOWER(A2:A),
     TEXTJOIN("|", 1, LOWER(E:E)))), E:F, 2, 0)))}
    

    0