#N/A in INDEX/MATCH: Row and Column Array Mismatch

In an INDEX/MATCH formula, the #N/A error occurs when the MATCH function fails to locate the search criteria within its designated lookup vector. When building two-way lookups or multi-cell calculations, array dimension mismatches—such as misaligned coordinate ranges or differing vector heights—frequently cause MATCH to return #N/A or force INDEX into a position it cannot calculate.

Quick Answer

An #N/A in INDEX/MATCH means MATCH did not find the lookup value. Ensure your MATCH lookup array is a single row or column, explicitly set the match type to 0 for exact matches (MATCH(val, range, 0)), and verify that the lookup range matches the exact length of the INDEX return array.

What You Need to Know: How INDEX and MATCH Coordinate

A standard one-way lookup combines the two functions:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

• MATCH searches lookup_range and returns the relative numerical position of lookup_value (e.g., Row 4).
• INDEX takes that relative position and retrieves the item at that position within return_range.

In a two-way lookup, two MATCH functions supply coordinates simultaneously:
=INDEX(grid_range, MATCH(row_val, row_lookup_range, 0), MATCH(col_val, col_lookup_range, 0))

For this formula to evaluate successfully, each MATCH function must scan a 1-dimensional array, and the relative indexes returned must map directly to the dimensions of the INDEX array.

The Key Factors Behind Array Mismatches

1. Dimension Orientation (Transposition Errors):MATCH expects a one-dimensional array (either a single row or a single column). Supplying a multi-row, multi-column range to MATCH (e.g., MATCH(A1, B2:E10, 0)) often produces #N/A depending on Excel’s calculation engine.
2. Unequal Range Boundaries (Offset Skewing):
   If INDEX references rows 2 through 100 (A2:A100), but MATCH searches rows 1 through 100 (B1:B100), the arrays are misaligned by one row. While this may return shifted data rather than #N/A initially, any search for an item in Row 100 causes MATCH to return position 100—which does not exist in A2:A100 (a 99-row range), causing a formula break.
3. Missing Exact Match Flag (match_type):
   The third argument of MATCH defaults to 1 (less than). In this mode, the range must be sorted ascending. An unsorted list will cause MATCH to return #N/A prematurely.
4. Orientation Inversion in Two-Way Lookups:
   Placing the column header lookup in the row parameter of INDEX, or vice versa, causes row/column mismatches that return #N/A or wrong data.

Diagnostic & Troubleshooting Path

Step 1: Isolate the MATCH Formula

To locate the exact break, test the MATCH function independently.
Highlight the MATCH(...) portion inside your formula bar and press F9 (or re-enter the MATCH segment in a blank test cell):

=MATCH(A2, B2:B50, 0)

• If MATCH returns #N/A: The lookup value is either missing, contains mismatched formatting, or the range is incorrect.
• If MATCH returns a valid integer: The error is occurring inside INDEX or within a secondary MATCH statement.

Step 2: Harmonize Array Starting and Ending Points

Verify that the return_range of INDEX and the lookup_range of MATCH share the exact same row span:

• Incorrect (Mismatched Bounds):=INDEX(C2:C50, MATCH(A2, B1:B50, 0)) (Row 1 vs Row 2 start)
• Correct (Aligned Bounds):=INDEX(C2:C50, MATCH(A2, B2:B50, 0))

For structured tables, using table column references prevents this misalignment entirely:
=INDEX(Inventory[Price], MATCH(A2, Inventory[SKU], 0))

Step 3: Troubleshoot Two-Way Matrix Dimensions

When referencing a two-dimensional matrix, verify your coordinates:
=INDEX(B2:M100, MATCH(A10, A2:A100, 0), MATCH(B9, B1:M1, 0))

1. The first MATCH scans the vertical array (A2:A100) to supply the row index.
2. The second MATCH scans the horizontal array (B1:M1) to supply the column index.
3. The INDEX array (B2:M100) matches the vertical span of the row range (99 rows) and the horizontal span of the column range (12 columns).

If you are using two MATCH functions across intersecting axes, see the complete implementation guide in #N/A in Two-Way Lookups (Double MATCH).

What If Values Match Visually but the Error Stays?

If your array dimensions are identical and match_type is set to 0, but MATCH still returns #N/A, the issue is data type disparity:

• One range contains text numbers while the other contains true numbers. Review the solution in #N/A when searching Text stored as Numbers.
• For broad lookup diagnostics across multiple formulas, consult Troubleshooting #N/A Errors in Excel: The Ultimate Lookup Diagnostic Guide.

Closing

Array mismatches in INDEX/MATCH almost always stem from range offsets or an omitted match type argument. Keep your lookup and return vectors locked to identical starting and ending rows, always enforce exact match mode (0), and isolate the MATCH segment to confirm it returns a valid index position.