Two-Dimensional INDEX/MATCH/MATCH

All versionsINDEXMATCH

INDEX with two MATCHes is the classic, version-proof way to look up a value at the intersection of a row label and a column label — the rate for a given product and region, with no helper columns and no fragile column counting.

Quick formula: to find the value where row label H2 meets column label H3:

=INDEX(B2:E10, MATCH(H2, A2:A10, 0), MATCH(H3, B1:E1, 0))

The first MATCH finds the row, the second finds the column, and INDEX returns the value at that intersection.

Functions used (tap for the full reference guide):

INDEX MATCH

The example

A grid: products down the side, regions across the top. Find Gadget / West.

	A	B	C	D
1	Product	East	West	South
2	Widget	$10	$12	$11
3	Gadget	$20	$22	$21
4	Cable	$5	$6	$5

The formula

The Gadget/West value:

=INDEX(B2:D4, MATCH("Gadget", A2:A4, 0), MATCH("West", B1:D1, 0)) // row 2, column 2 → $22

How it works

Two MATCHes give INDEX its coordinates:

MATCH("Gadget", A2:A4, 0) finds the row position of the product — 2.
MATCH("West", B1:D1, 0) finds the column position of the region — 2.
INDEX(B2:D4, 2, 2) returns the value at row 2, column 2 of the data block — $22.
The 0 in each MATCH means exact match; change a label and the result follows.

Why INDEX/MATCH over VLOOKUP? It looks in any direction, doesn’t break when you insert columns, and the two-MATCH form handles a full grid — all in plain functions available in every Excel version.

Try it: interactive demo

Live demo

Pick a product and region; see the intersection value.

Product

Region

Value:

Variations

Modern nested XLOOKUP

The Excel 365 equivalent:

=XLOOKUP(H2, A2:A10, XLOOKUP(H3, B1:E1, B2:E10))

Approximate two-way

For numeric breakpoints, set MATCH type to 1 (and sort).

Return an entire row or column

Use 0 for the unused dimension:

=INDEX(B2:D4, MATCH(H2,A2:A4,0), 0)

Pitfalls & errors

#N/A from a missing label. A MATCH that can’t find its value errors. Check for trailing spaces and exact spelling on both labels.

Keep the MATCH ranges aligned to INDEX. The row MATCH range must line up with INDEX’s rows and the column MATCH with its columns, or you read the wrong cell.

Always use 0 for exact match. Omitting it defaults to approximate and can silently return the wrong row/column on unsorted labels.

Practice workbook

📊

Download the free Two-Dimensional INDEX/MATCH/MATCH practice workbook

A grid with live INDEX/MATCH/MATCH, the nested-XLOOKUP and whole-row variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I look up a value by row and column in Excel?

Use INDEX with two MATCHes: =INDEX(data, MATCH(row_label, rows, 0), MATCH(col_label, cols, 0)). The MATCHes find the row and column positions and INDEX returns the intersection.

Why use INDEX/MATCH instead of VLOOKUP for a grid?

INDEX/MATCH/MATCH looks up by both a row and a column label, works in any direction, and doesn't break when columns are inserted, which VLOOKUP can't do.

What's the Excel 365 equivalent?

Nested XLOOKUP: =XLOOKUP(row, rows, XLOOKUP(col, cols, data)) returns the same intersection value with no index numbers.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: INDEX · MATCH