How do I build a formula to select a Category based on different select keywords/phrases in a determined column?

Question

I'm hoping to build a formula to automatially select a category based on select keywords/phrases from an imported bank statement. I attached an editable copy of the Google Sheet I'm looking for help with:

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

Under the 'Transactions' sheet, I have imported the first 3 transactions from my monthly bank statement. I would like to build a formula that can reference the 'Description' fields (Column D7 downwards) for select keywords/phrases. I was hoping the formula could populate the 'Category' in Column B automatically, either referencing the drop down menu or Column B in the 'Categories' sheet.

For example, based off the first 2 transactions:

• If text from Column D in 'Transactions' sheet contains the phrase "Debit Google ADS", select category 'Advertising'.

• If text from Column D in 'Transactions' sheet contains the keyword "Spotify", select category 'Music'.

I would like to create a series of these rules to autopopulate as many of these categories/transactions as possible, I'm just uncertain of the process or where to begin. Where would I even insert a formula for this type of rule?

I believe I'm looking to do something similar to this question: Categorize text in Google Sheets based on matching partial text in the Category index

Answer 1

REGEXMATCH

Use REGEXMATCH to a predefined list of strings to see if they exist in the description string from your bank statement.

Named Range

Name	Range	Description
categoryName	Categories!B:B	Column with category names
categoryStr	Categories!D:D	Column with strings to match

Formula

The formula should be copied down the Category column in your sheet labeled Transactions. While a single formula could be adapted to populate all the rows, the current approach means that the data can be in any order and can be easily manipulated (e.g. custom sort).

The formula filters the "Categories Name" column Categories!B:B where there is a match with the strings on the same row of the "Bank Statement Keywords/Phrase" column Categories!D:D and your "Description" column Transactions!D6:Dx. Any resulting rows (values) are returned after being joined into a single string delimited by ", " (comma + space). Wrapping the formula in TEXTJOIN insures a single value is returned and not an array. If you explicitly, or accidentally, introduce multiple matches, you will immediately see that in the result: "<category>, <category>" instead of "<category>".

=TEXTJOIN(", ", TRUE, 
    MAP(categoryName, categoryStr, LAMBDA(name, str, 
       IF(AND(REGEXMATCH(INDEX(D:D,ROW()), str), str<>"", ROW(str)>5),
          name, IFERROR(1/0)))))

Formula

Named Ranges