Excel INDEX Errors: Common Causes, Meanings, and Fixes

An Excel INDEX error usually means the requested row or column falls outside the range (#REF!), an index argument is invalid (#VALUE!), or INDEX is returning an error already present in the source data. Check that the row and column numbers are valid for the exact range supplied; correcting the range or the calculation that produces those numbers usually fixes the problem.

What the INDEX function does

INDEX returns a value, or sometimes a reference, from a specified position in a range. Its row and column arguments count positions within the range you give it, not the worksheet’s row and column numbers. For example, the first row of a range is position 1 even if that range begins on worksheet row 20. This difference is a common cause of mistakes when formulas combine INDEX with lookup functions.

The array form uses a range and a row position, with an optional column position. In a two-dimensional range, the row position identifies a row inside that range and the column position identifies a column inside it. The reference form can address one or more areas and has an additional area number, but most everyday lookup formulas use the array form.

INDEX is often paired with MATCH: MATCH finds the position of a value, and INDEX returns the value at that position. Unlike a lookup formula that searches a table in a particular direction, INDEX and MATCH can be arranged to return a result from a column to the left or right of the lookup column. That flexibility is useful, but it also means a mismatch between the ranges can produce confusing results.

How INDEX positions are counted

Suppose B2:D5 contains four rows and three columns. INDEX counts the top-left cell, B2, as row 1 and column 1. The bottom-right cell, D5, is row 4 and column 3. The numbers in the formula refer to those positions within B2:D5; they are not worksheet coordinates.

With the following layout, the formula selects the value at the third row and second column of the range, which is 80:

Assumed layout: B2:D5 contains rows (10, 20, 30), (40, 50, 60), (70, 80, 90), and (100, 110, 120). The third row is worksheet row 4, and the second column is worksheet column C. =INDEX(B2:D5,3,2)

This example is a useful check whenever a result seems shifted. Count from the upper-left corner of the range, and verify that every INDEX or MATCH range starts and ends where intended. If MATCH searches A2:A10 but INDEX returns from B3:B11, the positions no longer line up: the first match position refers to A2, while position 1 in the return range refers to B3.

Common INDEX errors and what they mean

#REF! usually means the requested row or column is beyond the dimensions of the supplied range. A four-row range cannot return row 5. Deleting cells or changing a range reference can also leave a formula pointing at an invalid reference.
#VALUE! can appear when a row or column argument is text or another value Excel cannot use as a number. It can also arise from an invalid argument in a more complicated formula feeding INDEX.
#N/A commonly comes from MATCH not finding the requested item. INDEX itself may be working correctly: it is simply receiving an error from the position calculation. An error value already stored in the selected source cell can also be returned by INDEX.
#NAME? often indicates a misspelled function name or text in a formula that Excel does not recognize. Check the function spelling and any named ranges used by the formula.
#SPILL! can occur in Microsoft 365 when a formula returns an array that needs to occupy multiple cells but the destination area is blocked. This is most relevant when INDEX uses a zero row or column argument to return a whole row or column.

Not every error displayed beside an INDEX formula is caused by INDEX. Follow the formula’s inputs: a MATCH result, a referenced cell, or another nested calculation may already contain the error. Repairing the first error in that chain is more reliable than masking the final result.

Diagnose the range and index arguments

Start by selecting the formula cell and inspecting the formula bar. Identify the range given to INDEX, then count its rows and columns. Compare those dimensions with the row and column values being supplied. For a range with four rows and three columns, valid positive positions are 1 through 4 for rows and 1 through 3 for columns.

For instance, with B2:D5 as the four-by-three range, requesting row 5 is out of bounds and produces #REF!:

Assumed layout: B2:D5 contains four rows and three columns, so its last valid row position is 4. =INDEX(B2:D5,5,1)

That formula is syntactically valid, but its row request exceeds the range. The fix is not automatically to enlarge the range: first establish whether the intended result is in a fifth row, or whether the calculation producing 5 is off by one. If the data really does include another row, update the reference to include it. If the range should stay as it is, correct the row calculation.

If the row or column number comes from a cell, inspect that cell’s value and type. A cell that looks numeric may actually contain text, particularly if it was imported or preceded by an apostrophe. For example, when F2 contains the text third rather than a number, the row argument cannot be used as an index:

Assumed layout: B2:D5 is the source range, and F2 contains the text third. =INDEX(B2:D5,F2,2)

Check both the displayed value and the underlying contents of the index cell. If the value is calculated, inspect its formula and its precedents. Excel’s Formulas > Error Checking command can help identify formula problems, while tracing precedents can reveal which input is feeding an unexpected index. Correct the source or calculation rather than converting arbitrary text to a number unless that conversion reflects the data’s intended meaning.

Fixing INDEX with MATCH

In a lookup formula, the MATCH range and INDEX return range should usually have the same number of rows or columns and begin on corresponding records. Set MATCH’s final argument to 0 when you need an exact match; otherwise, MATCH may use approximate matching under conditions that can return an unexpected position.

Assumed layout: A2:A6 contains Asha, Ben, Mina, Omar, and Priya; B2:B6 contains their scores 84, 76, 91, 88, and 95; E2 contains Mina. MATCH finds Mina in position 3, and INDEX returns the third score, 91. =INDEX(B2:B6,MATCH(E2,A2:A6,0))

If the lookup value is absent, MATCH returns #N/A and the combined formula displays that error. First verify spelling, extra spaces, and whether one value is stored as text while the other is numeric. An error handler can provide a clearer message, but it should not conceal a broken range or an unexpected mismatch.

Assumed layout: A2:A6 and B2:B6 contain the names and scores listed above, and E2 contains Lena, who is not in A2:A6. The lookup fails, so the formula displays Not found instead of #N/A. =IFERROR(INDEX(B2:B6,MATCH(E2,A2:A6,0)),"Not found")

Use IFERROR only when a missing match is an expected outcome and the replacement message is helpful. Because IFERROR also catches other errors, it can hide a #REF! caused by a damaged range or a different problem in the source data. During troubleshooting, temporarily remove the error handler so the underlying error remains visible.

Zero arguments, arrays, and spill behavior

In the array form, a row number of 0 asks INDEX for the entire selected column of the range; a column number of 0 asks for an entire row. In Microsoft 365, formulas that return multiple values can spill into neighboring cells. The cells where the results need to appear must be empty and not blocked by merged cells or other content.

Assumed layout: B2:D5 contains the four-by-three sample table, and the formula is entered in an empty cell with room below it. It returns the four values in the second column of the range, namely 20, 50, 80, and 110, spilling into four cells in Microsoft 365. =INDEX(B2:D5,0,2)

If this produces #SPILL!, clear the obstructing cells or move the formula to an open area. In older Excel versions, array-returning behavior can differ and may require legacy array-formula handling; Microsoft 365’s dynamic arrays generally spill automatically. Excel for the web also supports dynamic arrays, though its available features can depend on the account and update channel. On Mac, the INDEX logic is the same, but ribbon layout and keyboard shortcuts can differ from Windows.

Ways to prevent errors

Good range design prevents many INDEX problems, especially in workbooks that grow over time. Consider converting a dataset into an Excel table so that references can expand as rows are added. When formulas use ordinary cell ranges, check their endpoints after inserting, deleting, or moving records. Avoid assuming that a row number returned from one range will work in a differently sized range.

Count the dimensions of the INDEX range and verify every requested position is within them.
Keep MATCH and INDEX ranges aligned to the same records, and use exact-match mode when an exact lookup is intended.
Check whether index values are numeric and whether the source data contains errors or inconsistent types.
Test formulas with a known value near the beginning, middle, and end of the data, not just one successful example.
Use IFERROR for a deliberate user-facing message, not as a substitute for diagnosing a faulty formula.

For newer workbooks, XLOOKUP can be easier to read for many lookup tasks, and it is available in Microsoft 365 and Excel 2021 or later. INDEX and MATCH remain useful when compatibility with older Excel versions matters, when a formula needs flexible row-and-column positioning, or when the lookup and return ranges do not follow the usual XLOOKUP layout. Choose the method that fits the workbook’s version requirements and makes the intended relationship easiest to audit.

Frequently Asked Questions

Why does INDEX return #REF! in Excel?
Most often, its row or column position is larger than the corresponding dimension of the selected range. Count the range’s rows and columns from its upper-left cell and confirm that the requested positions fit.

Why does INDEX return #N/A?
When INDEX is paired with MATCH, MATCH may not find the lookup value and passes #N/A into the combined formula. Check for missing values, extra spaces, inconsistent data types, and whether exact matching is required.

Can INDEX return a value to the left of the lookup column?
Yes. MATCH can find a position in one range while INDEX returns from a separate, aligned range to its left or right. Make sure both ranges cover the same records in the same order.

What does a zero row or column argument do?
In the array form, zero requests an entire column or row from the selected range. In Microsoft 365, the returned values may spill into adjacent cells, so the spill area must be clear.

Should I wrap every INDEX formula in IFERROR?
No. IFERROR can make a finished report easier to read when a missing result is expected, but it can also hide broken references and other faults. Diagnose the error first, then add an error message only if it is appropriate for the workbook.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.