Spreadsheets

INDEX MATCH to look up a category from a merchant list

Look up a category from a merchant list with INDEX and MATCH, including the case where the category sits left of the merchant and VLOOKUP cannot work.

Editorial illustration for INDEX MATCH to look up a category from a merchant list

INDEX and MATCH together look up a category from a merchant list even when the category column sits to the left of the merchant column. The formula is =INDEX(Rules!$A$2:$A$200,MATCH($B2,Rules!$B$2:$B$200,0)): MATCH finds the merchant's position in one column, and INDEX returns the entry at that position from another.

Why would VLOOKUP fail in this layout?

Say your Rules sheet was built with Category first in column A, Merchant in column B and a notes column in C. VLOOKUP always searches the first column of its table and can only return something to the right of it, so it cannot search merchants in B and hand back categories from A. You would have to rearrange the sheet. INDEX and MATCH read two separate columns, so the order does not matter.

How do the two functions work together?

  1. MATCH($B2,Rules!$B$2:$B$200,0) returns a position: where the merchant sits in the merchant column. The 0 means exact match.
  2. INDEX(Rules!$A$2:$A$200, that position) returns the category from the same position in the category column.

Take a small invented list in B2:B6 of Swiggy, Metro card recharge, Big Basket, Pharmacy and Society maintenance, with categories in A2:A6. MATCH("Big Basket",B2:B6,0) returns 3, the third item. INDEX(A2:A6,3) then returns the third category, which is the one in A4, say Groceries. The two columns must be the same height and start on the same row, or the positions drift apart.

What happens when you insert a column?

Suppose you add a Date added column to the Rules sheet, between Category and Merchant. The VLOOKUP version, with its fixed 2, now returns the wrong column without any warning. The INDEX and MATCH formula adjusts: Excel shifts the merchant range from B to C and leaves the category range in A, so the answers stay correct. That robustness is the reason many people prefer it for sheets that grow for years.

  • Keep both ranges the same height, such as rows 2 to 200 in each.
  • Avoid whole-column ranges inside MATCH in a big sheet, as they slow recalculation.
  • Test with a merchant at the top of the list, one at the bottom and one that is missing.
FunctionLooksStrengthLimit
VLOOKUPRight of the lookup column onlyShort and familiarColumn number breaks when you insert columns
INDEX with MATCHAny directionSurvives inserted columnsTwo functions to read
XLOOKUPAny directionShort, built-in not-found textExcel 365 or 2021 only

How do you show a message when nothing matches?

Add IFNA: =IFNA(INDEX(Rules!$A$2:$A$200,MATCH($B2,Rules!$B$2:$B$200,0)),"Uncategorised"). A missing merchant then reads Uncategorised, and real faults, such as a deleted range, still show as errors so you notice them.

Can it fetch a second answer from the same match?

Yes, and that is a quiet advantage. If Rules also has a column D for the reporting head you use, the same MATCH can be reused with another INDEX: =INDEX(Rules!$D$2:$D$200,MATCH($B2,Rules!$B$2:$B$200,0)). To avoid repeating the search, calculate MATCH once in a helper column, say F, and write =INDEX(Rules!$A$2:$A$200,$F2) and =INDEX(Rules!$D$2:$D$200,$F2). Check any reporting head against your accountant's own list before relying on it.

What stays the same as other lookups?

  • Exact match is needed, so keep the last argument of MATCH at 0.
  • Whole-cell matching still applies, so a long narration will not match a short merchant name.
  • Trailing spaces and text-stored numbers still break matches. Use TRIM.

Finally, name the two ranges, for instance RuleCategory and RuleMerchant, through the Name Box. The formula then reads =INDEX(RuleCategory,MATCH($B2,RuleMerchant,0)), which is easier to audit months later, and extending a range once updates every formula that uses it.

If you find yourself maintaining several lookup columns, SpendixAI does categorisation in stages, starting from your own rules and history. Your corrections become history for the next import.

SPENDIXAI JOURNAL

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

Explore your reports