INDEX and MATCH Explained: The Lookup That Beats VLOOKUP

INDEX and MATCH Explained: The Lookup That Beats VLOOKUP featured illustration

📎 This article includes 1 downloadable practice file ↓

⏱ 2 min readUpdated 5 October 2026

VLOOKUP breaks when columns move, can’t look left and counts columns by hand. INDEX/MATCH fixes all three and works in every Excel version, which is why analysts swear by it even now that XLOOKUP exists.

In this article
  1. MATCH: where is it?
  2. INDEX: what’s at that position?
  3. Together
  4. Two-way lookup
  5. Multiple criteria
  6. Approximate match
  7. XLOOKUP vs INDEX/MATCH
  8. Continue learning

MATCH: where is it?

=MATCH("P103", A2:A50, 0)      ' position of P103 in the list, e.g. 3

The 0 means exact match. MATCH returns a position, not a value.

Advertisement

INDEX: what’s at that position?

=INDEX(C2:C50, 3)              ' the 3rd value in C2:C50

Together

=INDEX(C2:C50, MATCH(F2, A2:A50, 0))     ' price for the code in F2

Return column (C) and lookup column (A) are separate ranges, so inserting columns can’t break it, and the return column can be left of the lookup column.

Two-way lookup

=INDEX(B2:M20, MATCH(P2, A2:A20, 0), MATCH(P3, B1:M1, 0))   ' row by product, column by month

Multiple criteria

=INDEX(D2:D500, MATCH(1, (A2:A500=G2) * (B2:B500=G3), 0))

Multiplying conditions gives 1 only where both are true. In Excel 2019 and older, confirm with Ctrl+Shift+Enter.

Approximate match

MATCH(B2, Limits, 1) finds the band for a value in an ascending list of lower limits (slabs, grades).

💡 Lock the ranges with $ (or use Table columns) before copying INDEX/MATCH down; sliding ranges are the most common cause of wrong results.

XLOOKUP vs INDEX/MATCH

INDEX/MATCH XLOOKUP
Works in Every version 365 / 2021+
Readability Two functions One function
Not-found message Wrap in IFNA Built in
Two-way Natural Nested XLOOKUP

Use XLOOKUP when everyone has Excel 365; INDEX/MATCH when files go to people on older versions. See two-way lookups.

⚡ Interactive Practice Lab & Formula Tester:

Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.

Continue learning

All Excel Formulas guides →
Dependent Dropdown Lists in Excel (Two Methods That Actually Work)Create a second dropdown that changes with the first — Category then Product, Country…Why 0.1 + 0.2 Is Not Exactly 0.3 (and Why Excel Sometimes Agrees)Computers store decimals in binary, so 0.1 + 0.2 becomes 0.30000000000000004. Why it happens,…Excel’s Strangest Behaviours Explained: 15 “Bugs” That Are Actually FeaturesWhy Excel turns 1-2 into a date, drops leading zeros, shows ####, changes long…Why Does Excel Have Exactly 1,048,576 Rows? The Logic Behind Software LimitsWhy Excel stops at 1,048,576 rows and column XFD, why old Excel had 65,536…

📎 Practice files for this article

  • 📗
    Lookup scenarios workbookVLOOKUP, HLOOKUP, INDEX/MATCH and XLOOKUP side by side — vertical, horizontal, two-way — plus 6 real failure cases and their fixes, with PASS/FAIL checks.
    ⬇ XLSX · 10 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong