The INDEX + MATCH + MATCH guide
=INDEX($B$2:$G$7, MATCH(I2, $A$2:$A$7, 0), MATCH(J2, $B$1:$G$1, 0)) finds the product's row and the month's column, and returns the number where they cross.
How it works
A table with products down the side and months across the top has one number for each pair. Two questions find it: which row is the product on, and which column is the month in. MATCH answers each one with a position, and INDEX takes both: INDEX(table, row, column).
=INDEX(numbers, MATCH(product, product column, 0), MATCH(month, month row, 0))
For Footrest in April: Footrest is the 4th product in A2:A7, April the 4th month in B1:G1, so INDEX returns the 4th row and 4th column of B2:G7.
Line the ranges up
Each MATCH counts from the start of its own range, and INDEX counts from the start of its table. They have to start in the same place:
- The product range (
A2:A7) starts on the same row as the numbers (B2:G7). - The month range (
B1:G1) starts in the same column as the numbers.
Start the product range at the header, A1, and every product lands one row too low. Give INDEX the whole table with its headers, A1:G7, while the MATCHes skip them, and the answer is off by a row and a column. Both mistakes return a number, so nothing looks broken.
Row first, then column
INDEX takes the row number before the column number. Swap the MATCHes and you ask for the month's position as a row and the product's as a column. On this table that still returns a number, from the wrong cell. When the two positions happen to be equal, as for Footrest in April, the swapped formula is even right, which makes the mistake hard to spot.
The XLOOKUP version
In Excel 365 and 2021 or later, one XLOOKUP inside another does the same: =XLOOKUP(J2, $B$1:$G$1, XLOOKUP(I2, $A$2:$A$7, $B$2:$G$7)). The inner one returns the product's whole row, and the outer one picks the month from it.
Common mistakes
- The MATCHes the wrong way round. Row first.
- Ranges that don't line up. A header in one range and not the other puts every answer one row or column off.
- Leaving out the 0. Neither the products nor the months are sorted, so approximate MATCH returns wrong positions or #N/A.
- Not locking the ranges. Filled down, all three slide. Lock them with $.
One direction first: INDEX and MATCH.
FAQ
- What is INDEX MATCH MATCH in Excel?
- A two-way lookup: one MATCH finds the row of a value, another finds the column of a heading, and INDEX returns the cell where that row and column cross.
- Which MATCH goes first in INDEX MATCH MATCH?
- The row MATCH. INDEX takes the row number first and the column number second.
- Why is my INDEX MATCH MATCH off by one?
- A range starts in a different place from the others, usually because it includes the header. The MATCH ranges must start on the same row and column as INDEX's table.
- Can XLOOKUP do a two-way lookup?
- Yes. Nest one in another: =XLOOKUP(month, header row, XLOOKUP(product, product column, table)).



































































