Formula Fixer

INDEX MATCH Explained: Lookups Left, Right and 2-Way

Updated 2026-11-03

INDEX MATCH is the classic alternative to VLOOKUP. It works in every version of Excel and in Google Sheets, it can look in any direction, and it does not break when someone inserts a column. It looks harder than it is: it is simply two small functions working together.

The two pieces

INDEX returns the value at a given position in a range:

=INDEX(range, row_number, [column_number])

=INDEX(C2:C100, 5) returns the fifth value in C2:C100, which is the value in C6.

MATCH returns the position of a value in a range:

=MATCH(lookup_value, lookup_range, 0)

=MATCH("SKU-104", A2:A100, 0) returns 3 if SKU-104 is in A4. The final 0 means exact match. Always type it: if you leave it out, MATCH assumes the list is sorted and can return a wrong answer without any error.

Putting them together

Feed the position from MATCH into INDEX:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Example: product codes in A2:A100, prices in C2:C100 and the code you want in F2:

=INDEX($C$2:$C$100, MATCH(F2, $A$2:$A$100, 0))

Read it from the inside out: find the row where column A equals F2, then return the price from that row. The return range and the lookup range must be the same size and start on the same row.

Looking to the left

VLOOKUP can only return columns to the right of the column it searches. INDEX MATCH does not care about direction. If names are in column B and the employee IDs you search are in column E:

=INDEX($B$2:$B$500, MATCH(H2, $E$2:$E$500, 0))

Because each range is separate, inserting or deleting columns between them does not break the formula.

Two-way lookup: row and column

For a grid with months across the top and regions down the side, use one MATCH for the row and one for the column. Values in B2:M10, region names in A2:A10, month names in B1:M1, the region you want in P2 and the month in Q2:

=INDEX($B$2:$M$10, MATCH(P2, $A$2:$A$10, 0), MATCH(Q2, $B$1:$M$1, 0))

This pattern suits price grids, shipping rate tables, budget sheets and size charts.

Matching on more than one column

To find a row where Region (column A) and Product (column B) both match, returning Sales from column D:

=INDEX($D$2:$D$500, MATCH(1, ($A$2:$A$500=G2) * ($B$2:$B$500=H2), 0))

Each comparison produces a list of TRUE and FALSE values. Multiplying the lists gives 1 only where both conditions are true, and MATCH finds the first 1.

If several rows match, you get the first one. To add up every matching row instead, use SUMIFS.

Tiered rates with approximate match

For commission bands, discount levels or shipping weight tiers, an approximate match is exactly what you want. Put the lower limit of each tier in ascending order in A2:A6 and the rate for each tier in B2:B6:

=INDEX($B$2:$B$6, MATCH(E2, $A$2:$A$6, 1))

Match type 1 finds the largest value that is less than or equal to E2. With lower limits of 0, 1000, 5000, 10000 and 50000, a sale of 7,200 lands in the 5,000 tier. The limits must be sorted from smallest to largest, and the first one should be 0 so that small values still find a tier.

INDEX MATCH or XLOOKUP?

XLOOKUP does all of this in a single function and is easier to read, but it needs Microsoft 365, Excel 2021 or later, or Google Sheets. If your file goes to people with older Excel versions, INDEX MATCH is the safe choice. Compare the two in XLOOKUP vs VLOOKUP.

Fixing errors

← More guides