Spreadsheets

VLOOKUP vs XLOOKUP vs INDEX MATCH for expense categories

Compare VLOOKUP, XLOOKUP and INDEX MATCH on a merchant-category table: readability, left lookups, missing values and older Excel or Sheets support.

Editorial illustration for VLOOKUP vs XLOOKUP vs INDEX MATCH for expense categories

Use XLOOKUP if you have Excel 365, Excel 2021 or Google Sheets, because it reads clearly, looks up in any direction and has a missing-value argument built in. Use INDEX with MATCH when you must support older Excel. Keep VLOOKUP for simple tables where the answer sits to the right of the key and nobody will insert columns.

The same job, three ways

Say your merchant table sits in A2:B200, with the merchant in column A and the category in column B, and the narration you are checking is in E2.

  • VLOOKUP: =VLOOKUP(E2,$A$2:$B$200,2,FALSE)
  • XLOOKUP: =XLOOKUP(E2,$A$2:$A$200,$B$2:$B$200,"Uncategorised")
  • INDEX MATCH: =INDEX($B$2:$B$200,MATCH(E2,$A$2:$A$200,0))

All three return the category for an exact match. The differences show up when the table changes shape or the data is untidy.

How do they compare side by side?

PointVLOOKUPXLOOKUPINDEX MATCH
ReadabilityShort, but the 2 hides which columnNames the key column and the answer columnTwo functions to read together
Look to the leftNot possiblePossiblePossible
Default matchApproximate unless you type FALSEExact unless you change itExact only if the last argument is 0
Missing merchantShows #N/A, needs a wrapperFallback text is the fourth argumentShows #N/A, needs a wrapper
Insert a column in the tableThe column number may now point at the wrong columnSafe, since ranges move with the columnsSafe, since ranges move with the columns
Where it worksEvery Excel version and SheetsExcel 365, Excel 2021 and Sheets; not Excel 2019 or olderEvery Excel version and Sheets

Why can VLOOKUP give wrong answers quietly?

Two reasons. First, the column number is a typed digit. If you later insert a "Sub-category" column between merchant and category, the 2 now points at the sub-category, and the sheet keeps working with the wrong data. Second, leaving out FALSE makes it approximate, which on an unsorted merchant list can return a different merchant's category rather than an error.

What if the key is not the first column?

Say your table has the category in column A and the merchant in column B. VLOOKUP cannot look left of its key column. The other two do not mind:

  • =XLOOKUP(E2,$B$2:$B$200,$A$2:$A$200,"Uncategorised")
  • =INDEX($A$2:$A$200,MATCH(E2,$B$2:$B$200,0))

Without these, people rearrange the table just to suit VLOOKUP, which is how reference tables slowly get messy.

Which one should I use for expense categories?

  1. On Excel 365, Excel 2021 or Google Sheets, start with XLOOKUP. The fallback text means no IFNA wrapper.
  2. If the file is shared with someone on Excel 2019 or earlier, use INDEX MATCH wrapped in IFNA, so it opens correctly for everyone.
  3. If it is a quick one-off with a two-column table, VLOOKUP with FALSE is fine; the risk is small.
  4. If your narrations are messy, none of these fixes it. Clean them first, or match on keywords instead of exact names.

Before you pick one

  • Check which Excel version the other people using the file have
  • Anchor the table with dollar signs, as above, so copying the formula down does not slide the range
  • Test one merchant you know is missing, and confirm you see "Uncategorised" or #N/A
  • Keep the merchant table on its own tab, sorted A to Z, so duplicates are easy to spot

A lookup table is a fine way to learn how categorising works. When maintaining it starts to feel like a second job, SpendixAI offers rules, history-based matching and merchant aliases, which cover the same ground without formulas.

SPENDIXAI JOURNAL

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

Explore your reports