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.
try:
={"category"; ARRAYFORMULA(IFNA(VLOOKUP(IFNA(REGEXEXTRACT(LOWER(A2:A),
TEXTJOIN("|", 1, LOWER(E:E)))), E:F, 2, 0)))}