how to

INDEX/MATCH vs VLOOKUP: When and Why to Switch

· 3 min read spreadsheetsformulasvlookupindex-matchlookup

VLOOKUP is often the first lookup function people learn, but it has hard limits that bite in real workbooks. INDEX/MATCH does everything VLOOKUP does plus several things VLOOKUP cannot, at similar speed. Knowing exactly where the line sits saves you from restructuring sheets later.

What you need

  • A spreadsheet in Excel, Google Sheets, or LibreOffice Calc
  • A data table with a unique key column (ID, SKU, email)
  • At least one column to the right of the key you want to return
  • Basic comfort writing formulas

Step by step

  1. Write the baseline VLOOKUP on a test cell: =VLOOKUP(A2, Data!A:F, 3, FALSE). Note that it finds A2 in the first column of the range and returns the third column.
  2. Break it deliberately: move a new column into position two of the source table. The formula now returns wrong values because column index 3 shifted. This is the fragility you are escaping.
  3. Rebuild the same lookup as INDEX/MATCH: =INDEX(Data!C:C, MATCH(A2, Data!A:A, 0)). MATCH finds the row, INDEX returns the value from column C. Inserting columns between A and C changes nothing.
  4. Look leftward. Change the INDEX range to Data!A:A while matching on Data!D:D and confirm it returns values from a column left of the key. VLOOKUP cannot do this without array tricks.
  5. Test approximate matching if you use banding tables (tax brackets, shipping tiers). VLOOKUP with TRUE requires ascending sort of the key column. The safer equivalent is =INDEX(bands, MATCH(x, thresholds, 1)), which has identical sort requirements but keeps the threshold column separate from the result column.
  6. Handle misses identically in both: wrap in IFERROR, e.g. =IFERROR(INDEX(...,MATCH(...)), "not found"), so blanks do not cascade through dependent formulas.
  7. Benchmark on a large sheet. On 100k+ rows both are fast when ranges are bounded; unbounded whole-column references are the usual slowdown, so cap ranges regardless of function choice.
  8. Decide your default: use XLOOKUP where available (see [[xlookup-migration-guide]]), otherwise INDEX/MATCH for anything maintained by other people, and reserve VLOOKUP for quick one-off checks.

Common mistakes

  • Forgetting the fourth argument FALSE in VLOOKUP, which silently switches to approximate match
  • Using whole-column references inside MATCH on very large files, slowing every recalculation
  • Matching on non-unique keys, where both functions return only the first hit with no warning

Related guides

Once the lookup works, protect the source range so colleagues cannot reorder it, following [[protect-sheet-allow-cell-edits]]. To visualize lookup results across categories, pair them with a chart built using [[chart-design-rules]].

← All guides