INDEX MATCH MATCH: Two-Way Lookups Explained
By ExcelReady TeamUpdated 6 min read
The short answer
INDEX MATCH MATCH is a two-way lookup: =INDEX(B2:E6,MATCH(H1,A2:A6,0),MATCH(H2,B1:E1,0)). The first MATCH finds the row of the item in H1, the second finds the column of the heading in H2, and INDEX returns the value where they meet. It works in every Excel version and doesn't break when you insert columns.
Key takeaways
- The pattern is =INDEX(data, MATCH(row item, row labels, 0), MATCH(column item, column headers, 0)).
- The row labels must be the same height as the data, and the headers the same width.
- Always use 0 as MATCH's last argument for an exact match.
- In Excel 2021 or Microsoft 365, a nested XLOOKUP does the same job.
On this page+
INDEX MATCH MATCH is a two-way lookup: it finds the value where a row and a column meet, like the sales for East in Q3. One MATCH finds the row number, a second MATCH finds the column number, and INDEX returns the value at that crossing. This guide builds the formula step by step on a small sales grid, then covers the errors people hit most and the XLOOKUP alternative.
What INDEX MATCH MATCH does#
A normal lookup searches down one column. A two-way lookup searches down the rows and across the columns at the same time. You need it whenever your data is a grid, with labels down the side and headings across the top.
Here is the grid used in every example, quarterly sales by region in A1:E6:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Q1 | Q2 | Q3 | Q4 |
| 2 | North | 12,400 | 13,850 | 11,900 | 15,200 |
| 3 | South | 9,800 | 10,450 | 12,100 | 11,300 |
| 4 | East | 14,300 | 12,900 | 13,650 | 16,000 |
| 5 | West | 8,700 | 9,550 | 10,200 | 9,900 |
| 6 | Central | 11,100 | 11,800 | 12,500 | 13,400 |
The question: what were the sales for the region in H1 (East) in the quarter in H2 (Q3)? Looking at the grid, the answer is 13,650. The formula should find it by itself.
The three parts: INDEX, MATCH and MATCH#
Each function does one simple job.
INDEX returns the value at a given position in a range:
=INDEX(array, row_num, [column_num])=INDEX(B2:E6,3,3) returns the value in the 3rd row and 3rd column of B2:E6, which is 13,650. The INDEX function guide covers the details.
MATCH returns the position of a value in a list:
=MATCH(lookup_value, lookup_array, [match_type])=MATCH("East",A2:A6,0) returns 3, because East is the 3rd item in A2:A6. =MATCH("Q3",B1:E1,0) also returns 3, because Q3 is the 3rd heading in B1:E1. The 0 means exact match. The MATCH function guide and the article on the MATCH function explain the other match types.
Put the two MATCHes where the 3s were, and you have the full formula.
How to write an INDEX MATCH MATCH formula#
Build it in three steps so each part is easy to check. In H3:
- Start with INDEX and the data only, without labels or headers:
=INDEX(B2:E6, - Add the row MATCH, looking up H1 in the region labels:
MATCH(H1,A2:A6,0), - Add the column MATCH, looking up H2 in the quarter headings:
MATCH(H2,B1:E1,0))
The finished formula:
=INDEX(B2:E6,MATCH(H1,A2:A6,0),MATCH(H2,B1:E1,0))The result is 13,650. Change H1 to West and H2 to Q1, and it returns 8,700 without any other edit. That is the real power: one formula answers every region and quarter question.
How to line up the ranges#
The formula only works if the three ranges line up with each other. This is the rule most people miss:
| Range | Example | Must match |
|---|---|---|
| Data (INDEX) | B2:E6 | — |
| Row labels (first MATCH) | A2:A6 | Same rows as the data: 2 to 6 |
| Column headers (second MATCH) | B1:E1 | Same columns as the data: B to E |
If the row labels started at A1, East would be in position 4, and INDEX would return row 4 of the data, which is West. The answer would be wrong with no error to warn you. When you write the formula, check that the first and last row and column numbers match across all three ranges.
How to fill the formula across a summary table#
Lock the ranges with $ and you can fill one formula across a whole report. Say you list regions in G6:G7 and quarters in H5:I5. In H6:
=INDEX($B$2:$E$6,MATCH($G6,$A$2:$A$6,0),MATCH(H$5,$B$1:$E$1,0))- $G6 locks the column, so every cell in the row reads its region from column G.
- H$5 locks the row, so every cell in the column reads its quarter from row 5.
Fill right and down (Windows: Ctrl+R and Ctrl+D; Mac: ⌘R and ⌘D). Press F4 (Mac: ⌘T) while the cursor is in a reference to cycle through the $ options.
Common INDEX MATCH MATCH errors#
Most problems come from range sizes and exact matching.
- #N/A. A MATCH couldn't find its value. Check spelling, extra spaces ("Q3 " is not "Q3"), and numbers stored as text. Make sure both MATCHes end in
0. - #REF! A MATCH returned a position outside the INDEX range. This happens when the header range is wider than the data, such as
B1:F1with data inB2:E6. - A wrong value with no error. The label or header range is offset by one row or column. Re-check the alignment table above.
- Missing match type. Without the
0, MATCH assumes sorted data and can return a nearby item instead of the exact one. - Ranges move when filled. Add
$signs, as shown above.
The guide to fixing INDEX MATCH when it's not working goes through each error in more detail.
The XLOOKUP alternative#
In Excel 2021 or Microsoft 365, you can do the same two-way lookup by putting one XLOOKUP inside another:
=XLOOKUP(H1,A2:A6,XLOOKUP(H2,B1:E1,B2:E6))The inner XLOOKUP finds Q3 in the headers and returns the whole Q3 column, D2:D6. The outer XLOOKUP finds East in the region list and returns the matching value from that column: 13,650.
Which one should you use?
- INDEX MATCH MATCH works in every version of Excel and is a classic interview and test question.
- Nested XLOOKUP is easier to read and defaults to an exact match, but older Excel shows #NAME?.
For a full comparison, see INDEX MATCH vs XLOOKUP. If you're still choosing between lookups in general, VLOOKUP vs INDEX MATCH explains why INDEX MATCH is more flexible, and the guide to INDEX MATCH covers the one-way version.
Practise it#
Try the exercise on this page: write the formula in three steps and check that it returns 13,650. Then practise INDEX and MATCH in a live spreadsheet, and test yourself with the INDEX/MATCH skill check, where every formula is checked instantly.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Find the sales for the region in H1 and the quarter in H2. In H3, use INDEX with two MATCH functions on the data in B2:E6.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Q4 | East | |||||||
| 2 | Q3 | ||||||||
| 3 | Sales | ||||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 |
Tip: INDEX(B2:E6, row, column). For the row, MATCH H1 in A2:A6. For the column, MATCH H2 in B1:E1. Use 0 in both MATCHes.
Practise this in a lesson
Frequently asked questions
What is INDEX MATCH MATCH used for?
It finds a value in a grid by both its row and its column, such as the sales for East in Q3. One MATCH finds the row, the other finds the column, and INDEX returns the value at that crossing.
How do you write an INDEX MATCH MATCH formula?
Use =INDEX(data,MATCH(row_value,row_labels,0),MATCH(column_value,column_headers,0)). The data range must not include the labels or headers, and the label ranges must line up with it.
Why does my INDEX MATCH MATCH return #REF!?
One of the MATCH results is bigger than the INDEX range allows. This happens when the header range is wider, or the label range taller, than the data range. Make them the same size.
Why does INDEX MATCH MATCH return #N/A?
One MATCH couldn't find its value. Check spelling, extra spaces, numbers stored as text and that the last MATCH argument is 0. A heading like Q3 must match exactly.
Is XLOOKUP better than INDEX MATCH MATCH?
A nested XLOOKUP does the same two-way lookup and is easier to read, but it needs Excel 2021 or Microsoft 365. INDEX MATCH MATCH works in every version.