INDEX / MATCH

INDEX/MATCH is the most flexible lookup combo in Excel — works left, right, two-way, and multi-criteria. Every recipe, two-way lookup, and fix collected here.

5 pages · 1 reference, 3 how-to, 1 error fix

Reference

INDEX / MATCH

A flexible two-function lookup that can return values to the left of the lookup column.

How-to guides

How to do a two-way lookup

Use INDEX with two MATCH functions to look up a value at the intersection of a matching row and a matching column.

How to use INDEX MATCH with two criteria

Combine INDEX and MATCH with multiplication of two criteria arrays to perform a lookup that matches on two conditions simultaneously.

How to do a two-dimensional lookup

Combine INDEX with two MATCH calls — one for the row and one for the column — to look up a value at the intersection of a row and column header.

Error fixes

Fix #N/A

Fix #N/A errors in INDEX MATCH in Excel and Google Sheets — match_type wrong, arrays different sizes, data-type mismatches, and lookup value absent.