#N/A in Array Formulas: Unequal Array Lengths

August 6, 2026 5 min read

In Excel array formulas, an #N/A error occurs when two or more arrays of unequal sizes or mismatched orientations are paired element-by-element in a single calculation. When Excel evaluates operations across multiple ranges—such as adding, multiplying, or comparing arrays—it aligns the elements by index position. When a shorter array runs out of corresponding items, Excel has no data to evaluate against the remaining elements of the longer array. To signal this missing data, Excel pads the remaining positions with #N/A.

Quick Answer

Synchronize your range sizes so that all arrays in the formula span the exact same number of rows and columns (e.g., =A2:A10 * B2:B10, not =A2:A5 * B2:B10). If an array formula is calculating over vectors of unequal lengths, Excel automatically fills the missing positions with #N/A, which then spreads to downstream formulas like SUM or AVERAGE.

How Array Padding Generates #N/A

Modern Excel uses dynamic array arithmetic to perform element-by-element operations. If you feed ranges of different sizes into a single operation, Excel attempts to align them starting at index 1:

Consider the formula:
=A2:A4 + B2:B6

Where:

  • A2:A4 contains {10; 20; 30} (3 rows)
  • B2:B6 contains {1; 2; 3; 4; 5} (5 rows)

Excel processes the calculation row by row:

  1. Row 1: 10 + 1 = 11
  2. Row 2: 20 + 2 = 22
  3. Row 3: 30 + 3 = 33
  4. Row 4: [No Value] + 4 = #N/A
  5. Row 5: [No Value] + 5 = #N/A

The resulting output array spilled onto the sheet is:
{11; 22; 33; #N/A; #N/A}

If this array calculation is nested inside an aggregation function like =SUM(A2:A4 + B2:B6), the #N/A values inside the array corrupt the entire calculation, causing SUM to return #N/A instead of a total number.

Common Scenarios Where Unequal Lengths Occur

1. Inconsistent Range Selection in Multi-Condition Logic

When constructing multi-criteria calculations using boolean multiplication, users frequently misalign the bounding rows:

  • Broken Formula:=FILTER(C2:C100, (A2:A100="West") * (B2:B95="Apples"))
  • Why it breaks: The second criteria vector (B2:B95) is 5 rows shorter than the first vector (A2:A100). The operation (A2:A100="West") * (B2:B95="Apples") pads rows 96 through 100 with #N/A. When FILTER receives #N/A inside its inclusion argument, it breaks and returns an error.
  • The Fix: Align the row bounds:=FILTER(C2:C100, (A2:A100="West") * (B2:B100="Apples"))

2. Transposed Vector Collisions (Row vs. Column)

If you attempt to perform array arithmetic between a vertical column vector and a horizontal row vector, Excel does not match them 1-to-1. Instead, it performs two-dimensional expansion (broadcasting), creating a full matrix:

  • Column A1:A3 (3 rows, 1 column)
  • Row B1:D1 (1 row, 3 columns)
  • Formula: =A1:A3 & B1:D1 results in a 3×3 grid.

However, if you combine this with an operation that expects strict 1D pairing (such as helper functions inside MAP or SCAN), mismatched orientation triggers structural failures and #N/A padding. Always enforce matching orientations using TRANSPOSE or TOROW/TOCOL.

3. Unequal Helper Arrays in MAP / LAMBDA

When using modern iteration functions like MAP:

=MAP(A2:A10, B2:B20, LAMBDA(x, y, x * y))

MAP requires every supplied array to have identical dimensions. Because A2:A10 has 9 elements and B2:B20 has 19, MAP halts calculation immediately and returns #N/A or a calculation error across the entire spill range.

Step-by-Step Diagnostic Routine

If an array formula is outputting unexpected #N/A values:

  1. Audit Range Coordinates: Click into the formula bar. Excel highlights referenced ranges in different colors. Inspect the row numbers at the end of each range reference (A2:A50, B2:B50, C2:C50) to verify they terminate at the exact same row.
  2. Evaluate the Sub-Arrays:
    • Select a sub-expression in the formula bar (for example, (A2:A100="West") * (B2:B95="Apples")).
    • Press F9 to preview the evaluated array.
    • Look at the end of the array string. If you see ; #N/A; #N/A}, you have confirmed an unequal length mismatch.
    • Press Esc to exit without saving changes.

Long-Term Prevention: Use Structured Excel Tables

The most reliable way to prevent array length mismatches is to convert your raw data ranges into official Excel Tables (Ctrl + T).

Tables use structured references that refer to entire columns dynamically, making manual range coordination unnecessary:

  • Traditional Range (Fragile):=A2:A100 * B2:B98 (Prone to boundary typos)
  • Structured Table (Bulletproof):=Orders[Quantity] * Orders[UnitPrice]

Because structured column references always encompass the exact start and end rows of the table, their array lengths are guaranteed to remain identical. If new rows are added or deleted, both column vectors expand or contract simultaneously, eliminating array length #N/A errors entirely.

#N/A across External Workbook Links

August 5, 2026

#N/A in Data Validation Dropdown Lists

August 5, 2026

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

August 3, 2026