To fetch a category for a merchant, use =VLOOKUP($B2,Rules!$A$2:$B$200,2,FALSE). The four arguments are the merchant to find, the table to search, the column number to return from, and FALSE, which demands an exact match. Leave out FALSE and VLOOKUP may quietly return the wrong category, which is the commonest mistake in expense sheets.
What do the four arguments mean?
- Lookup value: $B2, the merchant on the current row. Dollar sign before B keeps the column fixed.
- Table array: Rules!$A$2:$B$200. The merchant column must be the first column of this range. Both axes are locked so the table does not creep when the formula is filled down.
- Column index: 2, the position of the category within the table. Column A is 1, column B is 2.
- Range lookup: FALSE (or 0) for exact match. TRUE or leaving it out means approximate.
Why is exact match essential for categories?
Approximate match is built for sorted numbers, such as grade bands. On a merchant list it assumes alphabetical order and returns the closest entry that sorts before your word. Say the Rules sheet holds Amazon, Swiggy and Zomato, in that order, and the narration is Uber. Alphabetically Uber falls after Swiggy and before Zomato, so approximate match returns Swiggy's category, Food delivery. No error, and a cab ride lands under food. With FALSE, VLOOKUP says #N/A and you know a rule is missing.
The locked range matters for a second reason. Without dollar signs, the table moves down as you fill the formula: row 3 would search Rules!A3:B201 and row 4 would search A4:B202. The first rows of your Rules list then drop out of the search, and merchants you added at the top of the sheet suddenly show as missing. If a formula works on row 2 and fails further down, look at the dollar signs first.
| Merchant looked up | With TRUE or omitted | With FALSE |
|---|---|---|
| Swiggy | Food delivery | Food delivery |
| Uber | Food delivery (wrong) | #N/A |
| Zomato | Food delivery | Food delivery |
How do you handle the not-found case?
Wrap the formula so a missing merchant says something useful: =IFNA(VLOOKUP($B2,Rules!$A$2:$B$200,2,FALSE),"Uncategorised"). IFNA catches only the #N/A error, which is exactly the not-found case. IFERROR catches every error, including a broken range, and would hide it. Both IFNA and VLOOKUP work the same way in Excel and Google Sheets.
What are the limits of VLOOKUP?
- It can only look to the right. The merchant column must come before the category column in the table.
- The column index is a hard-coded number. Insert a column in the Rules sheet between merchant and category and 2 now points at the wrong thing.
- It matches whole cells. A narration such as UPI-SWIGGY-12345@ybl will not match a rule that says Swiggy.
If any of those bite, XLOOKUP or INDEX with MATCH remove the column-number weakness, and a keyword search handles long narrations.
Quick practice
Make a Rules sheet with Amazon, Swiggy and Zomato and put a category beside each. Type Uber on an expense row and try the formula with TRUE, then FALSE, and watch the answer change. Do it once on invented data and the habit of typing FALSE sticks.
Before you fill it down
- Confirm the Rules sheet has no blank rows inside the table.
- Check the dollar signs on the table range.
- Test one known merchant and one that is missing.
- Count the Uncategorised rows with =COUNTIF($E$2:$E$500,"Uncategorised") and add rules for the largest ones.
If you inherit a sheet with a lookup that returns odd categories, press F2 on one formula cell and check the last argument before anything else. A missing FALSE is the culprit more often than people expect.
A lookup table is a good start, but the table needs tending. SpendixAI can do the first pass on imported statement rows using your own rules, then your history, and you correct what it gets wrong.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


