In Excel: use =INDEX(data, MATCH(row_value, row_labels, 0), MATCH(column_value, column_labels, 0)). The first MATCH locates the row, the second MATCH locates the column, and INDEX returns their one intersection.
On this page9
Syntax
Arguments
| Argument | required / optional | Description |
|---|---|---|
data | required | Values only: the matrix without its row or column headers. |
row_value | required | The label identifying the row to return. |
row_labels | required | A single column beside data, with exactly one cell per data row. |
column_value | required | The label identifying the column to return. |
column_labels | required | A single row above data, with exactly one cell per data column. |
Related functions
Worked matrix: product × month
Enter the product in F2 and the month in G2. The formula finds their row and column independently.
| Product | Jan | Feb | Mar |
|---|---|---|---|
| Widget | 12 | 15 | 18 |
| Gadget | 20 | 24 | 27 |
| Cable | 5 | 7 | 9 |
F2 = Gadget, G2 = Feb. The row MATCH returns 2; the column MATCH returns 2. Their intersection is C3 = 24.
=INDEX($B$2:$D$4,MATCH(F2,$A$2:$A$4,0),MATCH(G2,$B$1:$D$1,0))Method checked against Microsoft’s INDEX and MATCH documentation.
Put row labels in one column and column labels in one row; keep the numeric or text results in the rectangle between them.
Select only the value block as the first INDEX argument. For the worked matrix, that is $B$2:$D$4, not A1:D4.
Use MATCH(F2, $A$2:$A$4, 0) for the row. The final 0 requests an exact label match even when labels are unsorted.
Use a second MATCH(G2, $B$1:$D$1, 0) for the column. Its result is a position within the data block, not a sheet column letter.
Close INDEX and check the row and column MATCH calls separately. Here each returns 2, so INDEX returns C3, the value 24.
Keep the matrix and header ranges absolute with $ when filling the formula; leave F2 and G2 relative if each result row has different lookup labels.
What this does
A two-way lookup has two independent keys: one names a row, the other names a column. INDEX receives a rectangular block of values, not its headers. MATCH(row_value, row_labels, 0) supplies INDEX's row number; MATCH(column_value, column_labels, 0) supplies its column number. The two label ranges must line up exactly with the height and width of data. This is a different task from ordinary one-dimensional INDEX/MATCH, where only the row is looked up, and from a multiple-criteria lookup, where several tests select a record along one axis. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. For “index match match two-way lookup in excel”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected cells change, and only then apply the same setup to the full sheet. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That is what makes a data step that keeps your analysis trustworthy useful in real work: repeatable, auditable, and not dependent on memory or luck.
A worked example
Put product names Widget, Gadget and Cable in A2:A4 and months Jan, Feb and Mar in B1:D1. Fill B2:D4 with Widget 12/15/18, Gadget 20/24/27 and Cable 5/7/9. With Gadget in F2 and Feb in G2, =INDEX($B$2:$D$4, MATCH(F2, $A$2:$A$4, 0), MATCH(G2, $B$1:$D$1, 0)) returns 24 from C3: Gadget is row 2 of the data block and Feb is column 2. The visible matrix below lets you check both coordinates before copying the formula. Use this pattern when a table is genuinely a matrix, such as products by month or regions by quarter: changing either label changes the one value at their intersection without manually counting row or column numbers. Check the referenced cells, argument order and result type in your workbook. Start with the worked example before changing an input, then compare the changed result with the calculation you expected. An error value or a result of a different type is a reason to inspect the inputs and arguments.
In Google Sheets
Function availability: XLOOKUP: Excel 2021+. These are Microsoft's version markers; earlier perpetual releases do not include these functions. Check the actual product and update channel before using the formula. A marker does not establish an identical Web/Mac workflow or Google Sheets support. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. For “index match match two-way lookup in excel”, keep the input cells and the result visible while you check the formula in Excel. Follow the worked example and change one input at a time. The steps explain the workbook calculation; this page does not run a live calculator for that task.
Common mistakes
- Including the headers in the
INDEXdata block, which shifts the row and column positions and can return a plausible but wrong cell. - Using a row-label range with a different height from the data block, or a column-label range with a different width.
- Omitting either exact-match 0.
MATCHthen defaults to approximate matching and assumes sorted labels. - Treating two conditions on the same row as two-way lookup; the second
MATCHhere chooses a column header, not another row filter. - Using repeated row or column labels without noticing that
MATCHreturns the first exact occurrence.
Frequently asked questions
Why are there two MATCH calls?
The first finds a row position among row labels, and the second finds a column position among column labels. INDEX needs both positions to return a single cell from a two-dimensional data block.
Why does the formula return #N/A or a wrong value?
An absent label makes its MATCH return #N/A. If both labels exist but the value is wrong, check that the data block excludes headers and that each label range starts and ends alongside the corresponding data rows or columns.
Is this the same as INDEX MATCH with multiple criteria?
No. Multiple criteria test several fields to identify a row; the second MATCH here identifies a column header. A product-by-month matrix needs a row key and a column key.
Can I use XLOOKUP instead?
In an Excel version with XLOOKUP, nest horizontal and vertical lookups: =XLOOKUP(F2,$A$2:$A$4,XLOOKUP(G2,$B$1:$D$1,$B$2:$D$4)). The inner lookup chooses the month column and the outer lookup chooses the product row. XLOOKUP is unavailable in Excel 2016 and 2019.