To check your own list first and a general list second, put a second XLOOKUP in the if_not_found slot of the first: =XLOOKUP(F2,Overrides!$A$2:$A$100,Overrides!$B$2:$B$100,XLOOKUP(F2,Defaults!$A$2:$A$500,Defaults!$B$2:$B$500,"Uncategorised")). If the merchant is in Overrides you get that category; if not, Excel tries Defaults; if that also fails, you get Uncategorised.
Why keep two lists at all?
A general list holds the common merchants and their usual categories. Your personal list holds exceptions: the places where your use differs. Keeping them apart means you can paste a long default list without losing your own choices. The personal list always wins because it is checked first.
How does it work on five sample merchants?
Say Overrides holds Swiggy as Office lunches (because you order for work), and Amazon as Shopping. Defaults holds Swiggy as Food, Netflix as Entertainment, and DMart as Groceries. Here is what the formula returns for the lookups in column F:
| Merchant in F2 | In Overrides? | In Defaults? | Result |
|---|---|---|---|
| Swiggy | Yes, Office lunches | Yes, Food | Office lunches |
| Amazon | Yes, Shopping | No | Shopping |
| Netflix | No | Yes, Entertainment | Entertainment |
| DMart | No | Yes, Groceries | Groceries |
| Local chaiwala | No | No | Uncategorised |
Swiggy shows how the override beats the default. The chaiwala shows the last fallback.
How do I write it so it stays readable?
The nested version works everywhere XLOOKUP exists (Excel 365 and 2021), but it is long. LET names the pieces:
- =LET(m,TRIM(F2),XLOOKUP(m,Overrides!$A$2:$A$100,Overrides!$B$2:$B$100,XLOOKUP(m,Defaults!$A$2:$A$500,Defaults!$B$2:$B$500,"Uncategorised")))
LET stores the trimmed merchant as m so you type the cleaning step once. Add a third list the same way: put another XLOOKUP in the last if_not_found slot. Keep it to two or three levels; more than that and a rules table with a priority column is easier to manage.
Why do I sometimes get 0 instead of a category?
If the merchant is found in Overrides but its category cell is empty, XLOOKUP returns 0, not a blank, and it does not carry on to Defaults, because the lookup itself succeeded. Two defences:
- Add &"" to the first return array: Overrides!$B$2:$B$100&"" turns an empty cell into empty text.
- Delete override rows with no category, and check the list now and then.
If you want an empty override to fall through to the default, the nested formula alone cannot do it, and an IF with LET is needed: store the first lookup in a name, then use IF(name="",second lookup,name).
Test the whole chain with a short sample before you trust it on a year of data. Type five merchants into F2:F6, one from each situation in the table above, and confirm each answer by eye. It takes two minutes and catches a swapped range or a missing dollar sign before it reaches hundreds of rows.
Where should the two lists live?
Put them on separate sheets with plain headers, one merchant per row and no merged cells. Keep the merchant spelling in both lists identical to what your cleaned column produces, since XLOOKUP matches whole text, not part of it. If your statements print merchants as long strings such as UPI-SWIGGY-98765@ybl, clean them into short names first, and then look up the short names. Matching on part of a string is a different job that needs wildcards and more care.
What should I do with Uncategorised rows?
- Filter column G for Uncategorised once a week.
- Decide the category, and add the merchant to Overrides, not Defaults.
- Re-check totals, since the grand total should not change; only the split between categories does.
The two-level idea, your rules first and general knowledge second, is also how SpendixAI is built to categorise: your own rules, then your history and similar merchants, and an AI model only if you have it switched on.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


