Spreadsheets

Keyword rules in priority order: first match wins in a sheet

Make a sheet categorise by keyword rules where the first match wins, so Amazon Pay Electricity lands in Utilities before the generic Amazon rule.

Editorial illustration for Keyword rules in priority order: first match wins in a sheet

Keep your keywords in a table ranked from most specific to most general, then return the category of the first keyword the narration contains. That way "Amazon Pay Electricity" is filed under Utilities because its rule sits above plain "Amazon". The formula uses SEARCH, MATCH and INDEX, and it works in both Excel and Google Sheets with a small difference noted below.

Why does rule order matter?

A narration can contain several keywords at once. Say the line reads AMAZON PAY ELECTRICITY BILL. It contains "amazon", so a general Shopping rule matches. It also contains "amazon pay electricity", which should win. If the sheet checks every rule independently, the result depends on whichever formula runs last. A ranked table settles it: the first row that matches decides.

How do I set up the keyword table?

Make a tab called Rules with a keyword in column A, a category in column B and a priority number in column C. Sort the table by priority, lowest number first.

PriorityKeywordCategory
1amazon pay electricityUtilities
2amazon payWallet top-up
3amazonShopping
4swiggy instamartGroceries
5swiggyFood delivery

If you do not want to rank by hand, add a helper column D with =LEN(A2) and sort by it, longest first. Longer keywords are usually more specific, so this ordering is a sensible default; override it with priority numbers when it is not.

What is the first-match formula?

With the narration in B2 on your data tab and the rules in Rules!A2:A50 and Rules!B2:B50, this Excel formula returns the category of the first matching keyword:

=IFERROR(INDEX(Rules!$B$2:$B$50,MATCH(1,INDEX(ISNUMBER(SEARCH(Rules!$A$2:$A$50,B2))*(Rules!$A$2:$A$50<>""),0),0)),"Uncategorised")

Step by step:

  1. SEARCH(Rules!$A$2:$A$50,B2) looks for every keyword inside the narration and returns a position number, or an error when absent.
  2. ISNUMBER turns each result into TRUE or FALSE, and multiplying by (keyword<>"") removes blank rows in the table, which would otherwise match everything.
  3. MATCH(1, ..., 0) finds the first row holding a 1, which is the highest-ranked rule that fits.
  4. INDEX returns that row's category, and IFERROR supplies "Uncategorised" when nothing matches.

In Google Sheets, swap the inner INDEX(...,0) trick for ARRAYFORMULA: =IFERROR(INDEX(Rules!$B$2:$B$50,MATCH(1,ARRAYFORMULA(ISNUMBER(SEARCH(Rules!$A$2:$A$50,B2))*(Rules!$A$2:$A$50<>"")),0)),"Uncategorised"). In Excel 365 you can also use XMATCH(1, ..., 0) in place of MATCH.

What goes wrong with keyword rules?

  • A blank keyword row matches every narration, which is why the blank test is built into the formula
  • SEARCH is not case-sensitive, so you do not need LOWER, but it treats the characters ?, * and ~ as wildcards, so avoid them in keywords
  • A very short keyword such as "ola" will match inside longer words; add spaces around it or use a longer form
  • If you add a rule and the old category persists, check that the new row is inside the formula's range, here rows 2 to 50

Is this too much for a sheet?

A worked check: say you have 40 keywords and 300 transactions. That is 12,000 comparisons, which is nothing for a spreadsheet. The real cost is upkeep: new merchants appear every month, and the order needs a look each time you add a rule.

If you would rather not rank rules yourself, SpendixAI applies your own rules first, then your history and similar merchants, so a specific correction you made earlier outranks a general guess.

Monthly routine

  • Paste the new statement rows into the data tab
  • Filter for Uncategorised and add a rule for each repeating merchant
  • Place each new rule above any general rule that overlaps with it
  • Spot-check one row per category
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports