Spreadsheets

XLOOKUP to assign a category from the merchant name in Excel

Assign a category from the merchant name in Excel with XLOOKUP: a Rules sheet of merchants, an Uncategorised fallback and a list of what is left to sort.

Editorial illustration for XLOOKUP to assign a category from the merchant name in Excel

Keep a Rules sheet with one merchant per row and its category beside it, then in the expense sheet use =XLOOKUP($B2,Rules!$A$2:$A$200,Rules!$B$2:$B$200,"Uncategorised"). Excel looks for the merchant name, returns the category from the same position, and writes Uncategorised for anything it has never seen. XLOOKUP needs Excel 365 or Excel 2021.

How is the Rules sheet organised?

Column A holds the merchant name exactly as it will appear in your expense sheet, and column B the category. Start small and add a line whenever a new merchant turns up.

Rules!A (Merchant)Rules!B (Category)
SwiggyFood delivery
Metro card rechargeTransport
Big BasketGroceries
Society maintenanceHousing

What does the formula do, argument by argument?

  1. Lookup value: $B2, the merchant on this row. The dollar sign locks the column and lets the row move.
  2. Lookup array: Rules!$A$2:$A$200, where Excel searches. Locked on both axes so it does not slide down as you fill.
  3. Return array: Rules!$B$2:$B$200, the column the answer comes from. It must be the same height as the lookup array.
  4. If not found: "Uncategorised", the text shown when there is no match. Without it you get #N/A.

Match mode is exact by default, and case is ignored, so SWIGGY and Swiggy are the same. If a merchant appears twice on the Rules sheet, the first row wins.

Why do bank narrations not match?

A bank export rarely says Swiggy. It says something like UPI-SWIGGY-12345@ybl, and exact matching fails because the whole cell has to equal the rule. Two ways out. First, add a Merchant column to your expense sheet that holds a cleaned name, typed or built with text formulas, and look up that column. Second, move to keyword searches, which find a word inside a long narration, and that is a different formula from this one. Use plain XLOOKUP only when the merchant column is clean.

As an example of the first route, suppose the narration in A2 is UPI-SWIGGY-12345@ybl and the merchant sits between the first and second hyphen. In B2 use =TRIM(MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)). The first FIND locates the hyphen at position 4, the second at position 11, so MID takes 6 characters from position 5 and returns SWIGGY. That only suits narrations built to this one pattern; other banks and other payment types are laid out differently, so look at your own rows first.

How do you see what still needs a rule?

Count the leftovers with =COUNTIF($E$2:$E$500,"Uncategorised"). In Excel 365, list them with =UNIQUE(FILTER($B$2:$B$500,$E$2:$E$500="Uncategorised")). Each name that appears is a candidate for a new Rules row, and the sheet teaches itself over a few months. Use TRIM around the lookup value, =XLOOKUP(TRIM($B2),...), if a trailing space might hide a match.

A worked example with made-up rows

Merchant in the logAmountCategory returned
Swiggy₹420Food delivery
Big Basket₹1,860Groceries
Raju Tailors₹650Uncategorised
Society maintenance₹3,500Housing

Raju Tailors is not on the Rules sheet, so it falls through. Add a row for it with the category Clothing and it flips on the next recalculation. The categorised total here is 420 plus 1,860 plus 3,500, which is ₹5,780, and ₹650 is still waiting for a rule.

Next steps

  • Freeze the heading row of the Rules sheet and sort it alphabetically.
  • Keep merchant spellings consistent, since one typo creates a second, unmatched merchant.
  • Add a dropdown on the Category column of the Rules sheet so categories cannot drift.

That Rules sheet is a hand-built version of what SpendixAI does for imported statements: it applies your own rules first, then your history and similar merchants, and corrections you make become history. It is worth considering when the rules list gets long.

SPENDIXAI JOURNAL

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

Explore your reports