FormulaCraft

How to do a two-dimensional lookup in Excel and Google Sheets

Topic:INDEX / MATCH
Excel & Google Sheets
=INDEX($B$2:$D$4,MATCH("South",$A$2:$A$4,0),MATCH("Q2",$B$1:$D$1,0))
✓

Verified example

Computed by a real spreadsheet engine on the sample data below.

RegionQ1Q2Q3
North100012001100
South900950980
East110010501200
West850900870

=INDEX($B$2:$D$4,MATCH("South",$A$2:$A$4,0),MATCH("Q2",$B$1:$D$1,0))→950

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Runs server-side · free · no signup

Step by step

  1. 1Set up a grid with row labels in column A and column headers in row 1 (e.g. regions down the side, quarters across the top).
  2. 2Write the row MATCH: MATCH(rowLookup,$A$2:$A$100,0) to find which row the region is in.
  3. 3Write the column MATCH: MATCH(colLookup,$B$1:$Z$1,0) to find which column the quarter is in. Combine: =INDEX(data_range,rowMATCH,colMATCH).

Tips

Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.

Frequently asked

Can XLOOKUP do a two-dimensional lookup?

Yes. In Excel 365 and Google Sheets: =XLOOKUP(colLookup,$B$1:$D$1,XLOOKUP(rowLookup,$A$2:$A$10,$B$2:$D$10)) nests two XLOOKUPs for the same result.

More on INDEX / MATCH

See all →
Reference

INDEX / MATCH

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

How-to

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.

Error fix

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.

How-to

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.

Formulas used

INDEXMATCHXLOOKUPEXACTWRAPROWSWRAPCOLS

Related tasks

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: