Spreadsheets

Categorise a transaction when the narration contains a keyword

Categorise a transaction when the narration contains a keyword in Excel, with LOOKUP and SEARCH, an Excel 365 alternative and a UPI narration example.

Editorial illustration for Categorise a transaction when the narration contains a keyword

Put a list of keywords with their categories on a Rules sheet, then use =IFERROR(LOOKUP(2^15,SEARCH(Rules!$A$2:$A$30,$B2),Rules!$B$2:$B$30),"Uncategorised"). SEARCH hunts for each keyword inside the narration, LOOKUP picks the category of a keyword that was found, and a narration such as UPI-SWIGGY-12345@ybl is filed under the category of the keyword swiggy.

How does that formula work?

  • SEARCH(keywords, narration) tries every keyword in turn. It returns the position where the keyword begins, or #VALUE! when it is absent. For UPI-SWIGGY-12345@ybl and the keyword swiggy it returns 5, since the S is the fifth character. SEARCH ignores capitals; FIND does not.
  • LOOKUP(2^15, that list, categories) asks for the largest number not above 32768. Positions are always far smaller than that, so LOOKUP lands on the last number in the list. It skips the errors, so it finds the last keyword that matched.
  • IFERROR turns the all-errors case, with no keyword found, into Uncategorised.

Two things follow. If a narration contains several keywords, the one lowest in the Rules list wins, so put broad keywords at the top and specific ones below. And this form works in Excel without special keys. In Google Sheets, wrap SEARCH in ARRAYFORMULA and test a few rows before trusting it.

What does a worked example look like?

NarrationKeyword foundCategory
UPI-SWIGGY-12345@yblswiggyFood delivery
NEFT-SOCIETY MAINTENANCE-JULmaintenanceHousing
POS 4411 DMART KOCHIdmartGroceries
UPI-RAJU TAILORS-99812@okaxisnoneUncategorised

These narrations are invented. Real ones vary a great deal by bank, so look at your own statement and pick the stable word in each.

To see which keyword fired, use a helper column with the same idea but returning the keyword itself: =IFERROR(LOOKUP(2^15,SEARCH(Rules!$A$2:$A$30,$B2),Rules!$A$2:$A$30),"none"). When a row gets a surprising category, this column shows the keyword responsible, and you can usually fix it by reordering the list or lengthening the keyword. Capital letters do not matter, so you can type keywords in lower case and leave narrations as the bank writes them.

Why does a blank keyword cell cause trouble?

SEARCH with an empty keyword returns 1, because an empty string is found at the start of anything. A single blank cell inside the keyword range would then match every narration. Keep the range exact, with no blank rows, or point it at a named range that you extend by hand as you add keywords.

Is there an Excel 365 alternative?

Yes, one that reads more naturally:

=XLOOKUP(TRUE,ISNUMBER(SEARCH(Rules!$A$2:$A$30,$B2)),Rules!$B$2:$B$30,"Uncategorised")

SEARCH builds the list of positions, ISNUMBER turns that list into TRUE or FALSE, and XLOOKUP returns the category beside the first TRUE. Here the first keyword in the list wins, the reverse of the LOOKUP version, which is worth remembering when you order your Rules. It needs Excel 365 or 2021.

What next after the formula works?

  1. Filter for Uncategorised and read the narrations for words you can turn into keywords.
  2. Add keywords one at a time and check that old rows did not change category unexpectedly.
  3. Keep a copy of the Rules sheet before big edits.

Plan the order of the keyword list on purpose. Put generic words such as cafe or fuel near the top, and the specific merchants you care about lower, if you use the LOOKUP version, so a specific merchant overrides a generic word. Review the list every few months and delete keywords that no longer appear in your narrations; a short list is easier to reason about.

Keyword lists grow quickly. SpendixAI applies your own rules, then your history and similar merchants to imported statement rows, and hands what is left to an AI model only if you have switched that on. Until then, the sheet above is a fair way to see how it works.

SPENDIXAI JOURNAL

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

Explore your reports