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

Use INDEX with two MATCH functions: =INDEX(grid, MATCH(row_key, row_headers, 0), MATCH(col_key, col_headers, 0)). The first MATCH finds the row number, the second finds the column number, and INDEX returns the intersection.

Can XLOOKUP do a two-way lookup?

Yes, by nesting one inside the other: =XLOOKUP(row_key, row_headers, XLOOKUP(col_key, col_headers, grid)). The inner one returns a whole column, and the outer one picks the row from it.

What is the SUMIFS alternative to a two-way lookup?

If the data is long rather than a grid, =SUMIFS(Amount, Region, $A2, Month, B$1) does the same job and tolerates duplicates by summing them, which a lookup cannot.

Why does my two-way lookup break when rows are inserted?

Because a hard-coded row or column number moved. MATCH computes the position each time, so it survives insertions. Hard numbers, such as the col_index_num of a VLOOKUP, do not.

← All Kits · Excel Kit

Two-Way Lookup: Finding a Value by Row and Column at Once

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

Regions down the left, months across the top, numbers in the middle. Somebody wants the value for South in March, and they want it to keep working when a region is added.

What you do: INDEX with two MATCHes. It is the version that survives edits to the grid, and it is the one worth committing to muscle memory.

The short version. One MATCH for the row, one for the column, INDEX returns where they cross.

The grid

	Jan	Feb	Mar	Apr
North	10,200	10,900	11,400	10,700
South	7,300	7,050	7,600	7,900
East	11,800	11,200	12,000	11,650
West	5,900	6,400	6,100	6,800

Say the labels are in A2:A5, the months in B1:E1 and the numbers in B2:E5. South and Mar should return 7,600.

The three ways to write it

INDEX with two MATCHes, the one to learn

=INDEX($B$2:$E$5,
        MATCH($G$1, $A$2:$A$5, 0),
        MATCH($G$2, $B$1:$E$1, 0))

G1 holds the region, G2 the month. The first MATCH returns 2 for South, the second returns 3 for Mar, and INDEX returns the value at row 2, column 3 of the grid, which is 7,600. Both zeros mean exact match, and both are essential.

Insert a region or reorder the months and the formula still works, because nothing about the position is written into it.

Nested XLOOKUP, if you have it

=XLOOKUP($G$1, $A$2:$A$5,
         XLOOKUP($G$2, $B$1:$E$1, $B$2:$E$5))

The inner lookup returns the whole March column as an array, and the outer one picks South's row out of it. Shorter to read, and it only runs on Microsoft 365 and Excel 2021 and later. See XLOOKUP is not available before you put it in a shared file.

SUMIFS, when the data is long

=SUMIFS(Data[Amount], Data[Region], $G$1, Data[Month], $G$2)

This is the version to want. It needs the data in a long shape, one row per region and month, and in exchange it handles duplicates by summing them, returns 0 rather than an error for a missing combination, and lets you add a third or fourth condition without changing the shape of the formula.

The honest recommendation. If you own the data, unpivot the grid with Power Query and use SUMIFS. Two-way lookups exist mostly because somebody was handed a grid they were not allowed to change. That is a real situation, which is why the rest of this page exists.

The traps

Trap	Symptom	Fix
Grid range does not align with the header ranges	Off-by-one values, no error	Grid rows must match the label range exactly
Missing the 0 in MATCH	Wrong value, silently	Type the 0 every time
Ranges not locked	Errors appearing lower down	F4 on every range, or use table names
Merged header cells	MATCH finds nothing	Unmerge. Merged cells break most functions.
Duplicate row labels	Silently returns the first	Use SUMIFS, which adds them up
Header stored as text, key as a date	#N/A on every column	Make both the same type

The last one bites on month headers constantly. A header typed as Mar is text, and a cell holding a real date formatted as Mar is a number. They look identical and never match. Test with =ISTEXT(B1).

Making it a real interface

Put the two keys in labelled cells at the top, not inside the formula.
Give both cells a data validation list drawn from the headers, so a typo becomes impossible. See data validation.
Add a not-found message: =IFERROR(the formula, "no such combination"), once you know why it would fail.
Colour the two input cells differently from everything else. Inputs and outputs looking the same is how a reported number gets typed over.

How to apply this to your own work

Find the grids in your workbooks and ask which ones you are allowed to reshape. Reshape those.
For the rest, replace any hard-coded column numbers with MATCH, and note how many formulas that immunises against an inserted column.
Check every header row for merged cells and for text-against-date mismatches.
Move lookup keys into labelled input cells with validation lists.
Where duplicates are possible, use SUMIFS rather than a lookup, so two matching rows produce a sum instead of a silent first-match.

The one habit to keep

Ask whether the grid is a shape for reading or a shape for storing. Grids are excellent to look at and poor to compute from, and most two-way lookup problems are really a storage shape that should have been long.

How many of your formulas contain a column number typed by hand?

Every number here was worked before it was published. South and Mar is row 2, column 3 of the grid, which is 7,600.

A grid is a shape for reading, not a shape for calculating from.

Excel for Analysts is 378 pages that read every formula and dialog one at a time, so the workbook stops being a place where numbers appear and becomes one you can check.

Excel for Analysts, $19 →

The better answer is usually to unpivot the grid.

Power Query turns a grid into a long table in two clicks, after which a normal SUMIFS does the job. INDEX MATCH covers the one-way version.

Read Power Query →