To create a nested INDEX MATCH in Excel, you use one INDEX MATCH formula as the lookup value inside another INDEX MATCH formula, allowing you to look up a value based on two criteria (e.g., row and column). The syntax is: =INDEX(return_range, MATCH(lookup_value1, lookup_range1, 0), MATCH(lookup_value2, lookup_range2, 0)). This is the standard two-way lookup, and it replaces the older INDEX/MATCH with an array formula or a helper column.
What Is a Nested INDEX MATCH?
In Excel, INDEX returns a value from a specific row and column of a range. MATCH returns the position of a lookup value within a row or column. A nested INDEX MATCH combines these: the outer INDEX uses two MATCH functions—one to find the row number, another to find the column number. This is useful when your data is a table with headers in both rows and columns, and you need to find the intersection point.
For example, suppose you have a sales table with product names in column A (rows 2–10) and months in row 1 (columns B–G). To find the sales of “Widget” in “March”, you would use:
=INDEX(B2:G10, MATCH("Widget", A2:A10, 0), MATCH("March", B1:G1, 0))
Here, the first MATCH returns the row number where “Widget” appears, and the second MATCH returns the column number where “March” appears. The INDEX then returns the value at that intersection.
Why Use Nested INDEX MATCH Instead of VLOOKUP?
VLOOKUP is simpler but has limitations: it can only look to the right of the lookup column, it breaks when columns are inserted or deleted, and it requires the lookup column to be the first column in the table. Nested INDEX MATCH overcomes these:
- Lookup in any direction—you can look to the left or right, up or down.
- Column order doesn’t matter—you specify the return range directly.
- Two-way lookups—you can match both a row and a column, something VLOOKUP cannot do without helper columns or complex formulas.
- Better performance with large datasets because MATCH is faster than VLOOKUP’s linear search in some cases.
However, VLOOKUP is easier for beginners, so use it for simple one-way lookups. For two-way or dynamic lookups, nested INDEX MATCH is the better tool.
How to Build a Nested INDEX MATCH: Step-by-Step
Let’s create a two-way lookup with a real example. Assume you have a data range A1:E6:
A B C D E
1 Product Jan Feb Mar Apr
2 Widget 100 120 110 130
3 Gadget 200 210 220 230
4 Sprocket 150 160 170 180
5 Gizmo 300 310 320 330
6 Doodad 250 260 270 280
To find the value for “Gadget” in “Mar”:
- Write
=INDEX(B2:E6, ...)to set the return range (B2:E6). - Insert the first MATCH for the row:
MATCH("Gadget", A2:A6, 0)returns 2 (since Gadget is in row 2 of the range). - Insert the second MATCH for the column:
MATCH("Mar", B1:E1, 0)returns 3 (Mar is the third column). - Combine:
=INDEX(B2:E6, MATCH("Gadget", A2:A6, 0), MATCH("Mar", B1:E1, 0)).
The result is 220. You can replace the hardcoded “Gadget” and “Mar” with cell references, like H1 and H2, to make the formula dynamic.
Common Errors and How to Fix Them
Nested INDEX MATCH often fails for a few predictable reasons:
- #N/A error—the lookup value is not found. Check for extra spaces, non-printing characters, or data type mismatches (e.g., text vs. number). Use
=TRIM(A2)to clean data. - #VALUE! error—the MATCH ranges are not the same size as the INDEX range. Ensure each MATCH range is a single row or column that aligns with the INDEX range dimensions.
- Wrong column/row order—if your MATCH ranges are not properly aligned (e.g., row headers in column A, column headers in row 1), the formula returns a wrong value. Double-check the ranges.
- Exact vs. approximate match—always use
0for exact match unless you specifically need approximate (e.g., for grades). Using1or-1requires sorted data.
To debug, use the Evaluate Formula tool (Formulas tab > Formula Auditing > Evaluate Formula) to see each step, or temporarily replace the MATCH parts with numbers to test the INDEX.
Using Nested INDEX MATCH with Multiple Criteria (Array Alternative)
Sometimes you need to match on two criteria in the same row or column—for example, find the price where both “Product” and “Size” match. The classic solution is an array formula with MATCH(1, (range1=crit1)*(range2=crit2), 0), but in Excel 365 you can use XLOOKUP with concatenation or the FILTER function. However, a nested INDEX MATCH can also work if you create a helper column that concatenates the two criteria:
=INDEX(C2:C10, MATCH(1, (A2:A10="Widget")*(B2:B10="Large"), 0))
In older Excel, you must enter this as an array formula with Ctrl+Shift+Enter. In Excel 365, it works as a normal formula due to dynamic arrays. Alternatively, use XLOOKUP with a concatenated lookup: =XLOOKUP("Widget"&"|"&"Large", A2:A10&"|"&B2:B10, C2:C10). This is simpler and more modern.
Nested INDEX MATCH vs. XLOOKUP
XLOOKUP (Excel 2021/365) can do everything nested INDEX MATCH does, and more easily. For a two-way lookup, you can use:
=XLOOKUP("Widget", A2:A10, XLOOKUP("Mar", B1:G1, B2:G10))
This nested XLOOKUP returns the same result as the INDEX MATCH version. XLOOKUP has advantages: it has built-in error handling (the if_not_found argument), it defaults to exact match, and it’s easier to read. However, INDEX MATCH is still valuable for compatibility with older Excel versions (pre-2021) and for users who work with legacy workbooks. If you have Excel 365, prefer XLOOKUP for new formulas.
Frequently Asked Questions
Can I use nested INDEX MATCH to return a value from a different sheet?
Yes. Simply reference the other sheet in the ranges, e.g., =INDEX(Sheet2!B2:B10, MATCH(A1, Sheet2!A2:A10, 0)). The same nesting applies for two-way lookups across sheets.
Why does my nested INDEX MATCH return #REF!?
#REF! usually means the row or column number from MATCH is larger than the INDEX range’s dimensions. Check that your MATCH ranges are the same size as the INDEX range, and that the lookup values exist in those ranges.
Is nested INDEX MATCH slower than VLOOKUP?
For small datasets, the difference is negligible. For large datasets, INDEX MATCH can be faster because MATCH only searches one column/row, while VLOOKUP searches the entire table. However, XLOOKUP is often fastest in Excel 365.
Can I use wildcards in nested INDEX MATCH?
Yes, if you use 1 for approximate match or if you use wildcard characters with exact match (0). For example, MATCH("Wi*", A2:A10, 0) matches “Widget”. Wildcards work with text, not numbers.
How do I handle errors in nested INDEX MATCH?
Wrap the formula in IFERROR: =IFERROR(INDEX(...), "Not found"). This displays a custom message instead of #N/A. For more control, use the IFNA function to catch only #N/A errors.
Does nested INDEX MATCH work in Google Sheets?
Yes, the syntax is identical. Google Sheets supports INDEX and MATCH the same way, so you can copy the formula directly. Note that array formulas may require Ctrl+Shift+Enter in older Sheets, but modern Sheets handles them automatically.
Related
- Excel Eye Center Spanish Fork: Services, Doctors & More
- Excel Energy Group: What It Is and How It Works
- Free Excel Expense Tracking Sheet Template
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Paste in Excel When a Filter Is Applied SafelyTo paste into a filtered Excel list without changing hidden rows, select the destination cells, choose Home >…
- BMI Calculator for Excel: Formula, Setup, and TipsTo make a BMI calculator in Excel, enter height in centimeters in A2 and weight in kilograms in…
- Excel PV Factor Formula: Calculate Present ValueIn Excel, calculate a single-payment present value factor by dividing 1 by 1 plus the discount rate raised…
- Excel Formula for Counting Unique ValuesIn Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) to count distinct nonblank values in A2:A100. It returns 0 if…
- Excel Days Until Formula: Calculate a CountdownTo calculate calendar days until a date in Excel, put the target date in A2 and enter =A2-TODAY()…
- IFERROR in Excel: Syntax, Examples, and Common FixesIn Excel, IFERROR checks whether a calculation returns an error and displays a value you choose instead; its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.