Formulas - 2026-05-12

INDEX MATCH in Excel: Complete Guide

Learn INDEX MATCH in Excel with exact match examples, left lookup, two-way lookup, common errors, and when to use XLOOKUP instead.

Quick answer: index match excel

  • INDEX MATCH combines INDEX (value at position) with MATCH (position of a value). It beats VLOOKUP when the return column is to the left of the key or for two-way lookups.
  • Formula: =INDEX(C2:C100, MATCH(E2, A2:A100, 0))
  • Two-way lookups use two MATCH functions.
  • Decision: M365 one-way → XLOOKUP; legacy → INDEX MATCH.

Overview

  • INDEX MATCH combines position lookup with value retrieval. It predates XLOOKUP but remains essential for legacy files, two-way tables, and models where row and column logic are calculated separately.

One-way INDEX MATCH pattern

  • Return price: =INDEX(C:C, MATCH(E2, A:A, 0)).
  • MATCH third argument 0 = exact match for IDs.
  • Lock ranges with $ when copying across a summary sheet.

Two-way lookup (row + column labels)

  • Labels in P2 (row) and P3 (column); data in B2:M20.
  • =INDEX(B2:M20, MATCH(P2,A2:A20,0), MATCH(P3,B1:M1,0)).
  • No hardcoded row/column numbers when labels change.

INDEX MATCH vs XLOOKUP vs VLOOKUP

  • VLOOKUP: fastest to learn; key must be leftmost in table.
  • INDEX MATCH: flexible; works on older Excel.
  • XLOOKUP: best readability for new M365 one-way lookups.

What INDEX MATCH Does

  • INDEX MATCH is a classic Excel lookup combination. MATCH finds the position of a value, and INDEX returns the value at that position. Basic pattern: \\\ =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) \\\ The 0 in MATCH means exact match.

Basic INDEX MATCH Example

  • If product names are in A2:A100 and prices are in C2:C100: \\\ =INDEX(C2:C100, MATCH(E2, A2:A100, 0)) \\\ Excel finds E2 in A2:A100, then returns the matching value from C2:C100.

Why Use INDEX MATCH?

  • INDEX MATCH is useful because it can: Look left or right Avoid counting return columns Handle inserted columns better than VLOOKUP Support flexible row and column lookups

Left Lookup Example

  • VLOOKUP cannot return values to the left of the lookup column. INDEX MATCH can. If IDs are in C2:C100 and names are in A2:A100: \\\ =INDEX(A2:A100, MATCH(E2, C2:C100, 0)) \\\ This searches column C and returns from column A.

Two-Way Lookup

  • Use one MATCH for the row and another MATCH for the column. \\\ =INDEX(B2:E10, MATCH(H2, A2:A10, 0), MATCH(H3, B1:E1, 0)) \\\ This is useful for matrix-style reports, pricing tables, and monthly summaries.

INDEX MATCH vs XLOOKUP

  • Use XLOOKUP for cleaner modern formulas when available. Use INDEX MATCH when maintaining older workbooks or when the model already uses separate row and column matching. Related guides: INDEX function MATCH function XLOOKUP function INDEX MATCH vs XLOOKUP

INDEX MATCH Patterns

  • Goal Formula pattern Standard lookup =INDEX(ReturnRange,MATCH(Value,LookupRange,0)) Left lookup =INDEX(LeftRange,MATCH(Value,RightRange,0)) Two-way lookup =INDEX(Data,MATCH(RowValue,Rows,0),MATCH(ColumnValue,Headers,0))

Expert Guidance

  • INDEX MATCH is most valuable when you need a two-way lookup or when maintaining older workbooks that cannot use XLOOKUP. For new Microsoft 365 workbooks, XLOOKUP is usually easier for one-dimensional lookups. Two-way lookup example: \\\ =INDEX(B2:M20,MATCH(P2,A2:A20,0),MATCH(P3,B1:M1,0)) \\\ This returns the intersection of a selected row label and column header.

Related Guides

  • Compare INDEX MATCH vs XLOOKUP, review INDEX and MATCH, and use XLOOKUP for simpler modern lookups.

Decision matrix

  • Scenario Best choice New M365 one-way lookup XLOOKUP Legacy workbook INDEX MATCH Two-way intersection INDEX + MATCH + MATCH All matching rows FILTER

Frequently asked questions

  • Is INDEX MATCH better than VLOOKUP? INDEX MATCH is more flexible than VLOOKUP because it can look left, does not require column counting, and handles inserted columns better.
  • Should I use INDEX MATCH or XLOOKUP? Use XLOOKUP for most modern Excel workbooks. Use INDEX MATCH for older compatibility or complex two-way lookup structures.
  • Is INDEX MATCH still worth learning? Yes. It is still useful for older workbooks, two-way lookups, and models where row and column matching are separate.
  • Why is MATCH using 0 as the last argument? 0 tells MATCH to find an exact match, which is the safest option for IDs, names, and labels.