#REF! in INDEX: Row/Column index exceeds range boundaries

An #REF! error in an INDEX formula means the formula requested a row or column coordinate that falls outside the boundaries of the referenced array. Unlike an #N/A error—which indicates that a value cannot be found—#REF! in INDEX is an out-of-bounds error. For example, if your table contains 10 rows and your formula asks for row 11, INDEX cannot fulfill the request and returns #REF!.

Quick Answer

#REF! occurs when the row or column argument in INDEX is larger than the total dimensions of the target array, or when the index is less than 1. Check your nested MATCH functions; if MATCH scans a range larger than INDEX‘s array, it passes a coordinate that exceeds INDEX‘s boundary.

---

Anatomy of the INDEX Out-of-Bounds Error

The syntax for a standard two-dimensional INDEX function is:

=INDEX(array, row_num, [column_num])

Excel evaluates the boundaries of array before returning data:

       Array: B2:D11 (10 rows high, 3 columns wide)
       Valid row_num:    1 to 10
       Valid column_num: 1 to 3

If you request:

• =INDEX(B2:D11, 11, 2) $\rightarrow$ #REF! (Row 11 does not exist in a 10-row array)
• =INDEX(B2:D11, 5, 4) $\rightarrow$ #REF! (Column 4 does not exist in a 3-column array)
• =INDEX(B2:D11, 0, 0) $\rightarrow$ Returns the whole array (in modern Excel) or fails depending on context.
• =INDEX(B2:D11, -1, 1) $\rightarrow$ #VALUE! (Negative numbers trigger value errors)

---

The Most Common Culprit: Asymmetric MATCH Ranges

In professional workbooks, row_num and column_num are rarely hardcoded numbers. They are almost always calculated dynamically using MATCH.

The primary cause of #REF! in INDEX/MATCH formulas is a range size mismatch between the INDEX array and the MATCH array.

The Error Pattern

Consider this formula:

=INDEX(C2:C100, MATCH(E2, A1:A200, 0))

1. The INDEX array is C2:C100, which contains 99 rows.
2. The MATCH lookup array is A1:A200, which contains 200 rows.
3. Suppose the search value E2 is found in cell A150.
4. MATCH(E2, A1:A200, 0) successfully evaluates to position 150.
5. The formula simplifies to: =INDEX(C2:C100, 150).
6. Because C2:C100 only has 99 rows, row 150 falls outside the boundary. INDEX returns #REF!.

INDEX Range:  Row 2 ---------------- Row 100 (99 rows)
MATCH Range:  Row 1 --------------------------------- Row 200 (200 rows)
                                            ^
                               Match finds item at row 150
                               INDEX fails -> #REF!

The Fix

Ensure that your INDEX and MATCH ranges start on the same row and cover identical heights:

=INDEX(C2:C200, MATCH(E2, A2:A200, 0))

For a detailed breakdown of alignment issues between row and column lookups, see #N/A in INDEX/MATCH: Row and Column Array Mismatch.

---

Diagnostic Step: Isolating the Coordinate

When troubleshooting an #REF! error in a complex INDEX formula:

1. Select the cell displaying #REF!.
2. In the formula bar, highlight the expression supplied for row_num (e.g., MATCH(E2, A1:A200, 0)).
3. Press F9 to evaluate that segment.
4. Note the resulting integer (e.g., 150).
5. Press Esc to exit without saving changes.
6. Now inspect the INDEX array. In an empty cell, count the total rows in the array using =ROWS(array).
7. If the number from step 4 is greater than the number from step 6, you have located the boundary break.

---

Other Common Causes of #REF! in INDEX

1. Swapped Row and Column Arguments in 2D Lookups

In a two-dimensional lookup table, users often accidentally swap the order of the MATCH functions:

• Syntax: =INDEX(array, row_num, column_num)
• Mistake: =INDEX(B2:G20, MATCH(ColHeader, B1:G1, 0), MATCH(RowHeader, A2:A20, 0))

If the column header match returns 5, and that value is passed into the row_num argument, it might work until ColHeader returns a number larger than 19 (the total rows in B2:G20). As soon as the column index exceeds the total height of the array, INDEX breaks with #REF!.

2. Deleted Columns or Rows

If an entire column or row inside the source range was deleted using Right-Click > Delete, the formula itself may contain a corrupted reference:

=INDEX(B2:#REF!, 5, 2)

When Excel loses the anchor point for a cell range, it replaces the missing coordinates with #REF!. To repair broken ranges caused by cell deletions, refer to #REF! after Deleting Source Rows or Columns.

---

Long-Term Prevention: Use Structured Tables

The most effective safeguard against index boundary mismatches is converting your data into an official Excel Table (Ctrl + T).

Structured table references automatically bind coordinate lengths together:

=INDEX(SalesTable[Total], MATCH(E2, SalesTable[CustomerID], 0))

Because SalesTable[Total] and SalesTable[CustomerID] belong to the same table object, they are guaranteed to have identical row counts. Even if you add or delete hundreds of rows, the ranges expand and contract together, making it impossible for MATCH to return a row index that exceeds INDEX‘s boundaries.

For a comprehensive guide to repairing all structural #REF! errors across your workbooks, visit How to Fix #REF! Errors in Excel: A Comprehensive Structural Repair Guide. If you encounter index boundary problems in older lookup formulas, see #REF! in VLOOKUP: When col_index_num exceeds Table Array.