1

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.

Randy Adikara
  • 357
  • 3
  • 10

1 Answers1

2

try:

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

0

player0
  • 124,011
  • 12
  • 67
  • 124