How to Fix #N/A Error in a Two-Dimensional INDEX MATCH Formula
Question details
The user is trying to retrieve specific data from a two-dimensional matrix using an INDEX MATCH formula but is encountering an #N/A error.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Looking up a specific cell value at the intersection of a matched row (e.g., a consultant's name) and a matched column (e.g., a specific date) within a large data range.
- Observed behavior
- The formula returns an #N/A error, typically caused by swapped row and column MATCH arguments or misaligned array dimensions.
Verify that your lookup values exactly match the headers and row labels in your data matrix, as trailing spaces, typos, or mismatched data types (like text vs. dates) will trigger an #N/A error.
Correct the Row and Column MATCH Arguments
Reorder the MATCH functions so that the first one correctly identifies the row and the second one correctly identifies the column within your INDEX range.
The syntax for a two-way lookup is =INDEX(array, row_num, [column_num]). If you accidentally supply the column lookup where the row number should be, the formula will fail.
Select only the data grid you want to return values from, excluding the headers. For example: `Coordinators!$B$7:$CM$26`.
Use your vertical lookup value and match it exactly against the row labels array. For example: `MATCH($A26, Coordinators!$A$7:$A$26, 0)`. The `0` ensures an exact match.
Use your horizontal lookup value and match it exactly against the column headers array. For example: `MATCH(B$25, Coordinators!$B$6:$CM$6, 0)`.
Assemble the formula as `=INDEX(DataRange, RowMatch, ColumnMatch)`. For example: `=INDEX(Coordinators!$B$7:$CM$26, MATCH($A26, Coordinators!$A$7:$A$26, 0), MATCH(B$25, Coordinators!$B$6:$CM$6, 0))`. Press Enter to apply.
Ensure your ranges use `$` signs to lock the arrays appropriately, then click and drag the fill handle at the bottom-right of the cell to copy the formula across other cells.
Use the FILTER Function for Dynamic Arrays
If you are using a modern spreadsheet application that supports dynamic arrays, the FILTER function offers a cleaner alternative for returning matching rows or columns.
Master Complex Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced lookups, including INDEX, MATCH, XLOOKUP, and dynamic arrays. You can seamlessly troubleshoot and fix complex matrix lookup errors with its intuitive interface and powerful calculation engine.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the broken INDEX MATCH formula.
- 2. Evaluate the error: Select the cell showing the #N/A error. Navigate to the 'Formulas' tab and click 'Evaluate Formula' to see exactly which MATCH argument is failing.
- 3. Correct the arguments: Adjust the row and column ranges in the formula bar to match your matrix dimensions, then press Enter to recalculate.
- 4. Drag to fill: Double-click the fill handle in the bottom-right corner of the corrected cell to apply the fixed formula down the entire column.
Frequently Asked Questions
Why does my INDEX MATCH return a #REF! error instead of #N/A?
A #REF! error happens when your MATCH function returns a row or column number that is larger than the boundaries of your INDEX array. Double-check that your INDEX range covers all the rows and columns that your MATCH functions are referencing.
Can I use XLOOKUP for a two-dimensional matrix lookup?
Yes. You can nest one XLOOKUP inside another to perform a two-way lookup. The inner XLOOKUP retrieves the entire row or column as an array, and the outer XLOOKUP searches through that returned array to find the exact intersecting value.
How do I prevent the MATCH function from finding the wrong value?
Ensure the third argument in your MATCH function is set to 0, like this: MATCH(lookup_value, lookup_array, 0). This forces the formula to look for an exact match. If you omit this argument, it defaults to 1 (approximate match), which can return incorrect results if your data isn't sorted.
How do I fix #N/A errors when the values visually match?
If data looks identical but still triggers an #N/A error, one of your cells might contain hidden trailing spaces or different formatting (e.g., a number formatted as text). Use the TRIM() function or ensure both arrays are formatted identically as General or Text.
