Spreadsheets

Categorise transactions with XLOOKUP and wildcard keywords

Categorise bank transactions in Excel with a keyword table and XLOOKUP, learn why wildcard mode points the other way and how to order keywords.

Editorial illustration for Categorise transactions with XLOOKUP and wildcard keywords

To categorise transactions with a keyword table, put keywords in one column and categories in the next, then use XLOOKUP with ISNUMBER and SEARCH to return the category for the first keyword found inside each narration. Anything that matches nothing becomes Uncategorised. Order your keywords from most specific to most general, because the first match wins.

What should the keyword table hold?

Make a sheet called Keywords with two columns. Keep keywords short, in capitals, and without asterisks. The examples below are invented:

Keyword (F)Category (G)
GENERAL STOREGroceries
MEDICALHealth
PETROLFuel
ELECTRICITYUtilities
DAIRYGroceries

Which formula does the lookup?

With the narration in A2, keywords in F2:F100 and categories in G2:G100, use XLOOKUP in Excel 365 or 2021:

  • =XLOOKUP(TRUE,(F$2:F$100<>"")*ISNUMBER(SEARCH(F$2:F$100,A2)),G$2:G$100,"Uncategorised")

SEARCH(F$2:F$100,A2) tests every keyword against the narration, so it returns a position for each keyword found and an error for the rest. ISNUMBER converts that to TRUE or FALSE. Multiplying by the non-blank check stops empty cells from matching everything. XLOOKUP then looks for the first TRUE and returns the category beside it, or Uncategorised if there is none.

What does XLOOKUP's wildcard mode do instead?

The match_mode setting 2 turns on wildcards, but they work on the value you look up, not on the list. So XLOOKUP("*"&F2&"*",$A$2:$A$500,$B$2:$B$500,"",2) answers the opposite question: which narration contains the keyword in F2. That is handy for checking what a keyword would catch. It cannot tell you which of many keywords sits inside one narration, so asterisks around every keyword in the table achieve nothing for the formula above. SEARCH does that job.

What happens when two keywords match?

Take the invented narration BALAJI MEDICAL AND GENERAL STORE. Both GENERAL STORE and MEDICAL occur in it.

  • If GENERAL STORE sits higher in the table, the result is Groceries.
  • If MEDICAL sits higher, the result is Health.
  • XLOOKUP simply returns the first TRUE it meets, so the table order is the rule.

Decide your own rule and put the more specific, more reliable keywords first. A narration that mentions a pharmacy is likely health even when it also says store. Rows where more than one keyword matches deserve a look, and you can find them with =SUMPRODUCT(--ISNUMBER(SEARCH(F$2:F$100,A2))), which counts the matching keywords. Filter for 2 or more.

How do you improve it over time?

  1. Filter for Uncategorised and read the narrations.
  2. Add one keyword per merchant, using the shortest text that is unique to it.
  3. Move any keyword that causes a wrong match lower, or make it longer.
  4. Review the multi-match rows once a month.

How do you check the totals?

  • Add a SUMIFS per category: =SUMIFS($D$2:$D$500,$H$2:$H$500,"Groceries"), where H holds the category result.
  • Add Uncategorised to the list, so the category totals add up to the whole.
  • Compare the sum of all categories with =SUM($D$2:$D$500).

A worked check with invented numbers. Say a month's debits total ₹46,800. Your category totals come to Groceries ₹14,200, Fuel ₹6,500, Health ₹3,100, Utilities ₹5,400 and Uncategorised ₹17,600. Adding them gives 14,200 + 6,500 + 3,100 + 5,400 + 17,600 = ₹46,800, which matches. If the sum had come out lower, a row would have fallen through, perhaps because a cell in the amount column held text instead of a number. Uncategorised at more than a third of the total tells you the keyword list still has gaps, so work through the biggest unmatched amounts first rather than the oldest.

Keyword rules are the first stage of how SpendixAI categorises too: your own rules, then your history and similar merchants, and an AI model only for what is left, and only if you leave AI on. You can switch AI off, and unmatched items are then held for review.

SPENDIXAI JOURNAL

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

Explore your reports