Excel INDEX MATCH #REF! Error: Causes and Quick Fixes

Errors & fixes · Updated November 19, 2026

An Excel INDEX MATCH formula returns #REF! when INDEX is asked for a row, column, or reference area that does not exist, or when the formula contains a broken cell reference. Check that the lookup range and return range cover the same number of rows, and that MATCH is finding a position within the range INDEX uses; a size mismatch is a common cause.

What #REF! means in an INDEX MATCH formula

#REF! is Excel’s invalid-reference error. In an INDEX MATCH formula, MATCH searches a lookup range and returns a position; INDEX uses that position to select a value from a return range. The formula fails with #REF! if that requested position is outside the dimensions of the INDEX range, or if one of the references in the formula has been broken.

This is different from #N/A, which usually means MATCH did not find the requested value. With an exact-match setting, a missing lookup value normally produces #N/A, not #REF!. A #REF! points you toward the references and the positions being passed to INDEX, rather than simply telling you that a value was not found.

How INDEX and MATCH work together

MATCH returns a position counted from the first cell in its lookup range—not the worksheet row number. INDEX then uses that position within its own array. For example, if MATCH finds a value in the fifth cell of A2:A6, it returns 5, even though the matching cell is on worksheet row 6. INDEX needs an array with at least five rows to use that result.

Suppose product codes are in A2:A6, prices are in B2:B6, and the code to find is in E2. The rows contain P-101 through P-105, with prices 12, 18, 25, 31, and 44 respectively; E2 contains P-103. With that layout, this formula returns 25:

With product codes in A2:A6, prices in B2:B6, and the lookup code in E2, enter:

=INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0))

The final 0 tells MATCH to look for an exact match. The dollar signs keep the source ranges fixed if you copy the formula to other cells. They do not change which cells are searched when the formula is first calculated.

Check for ranges with different sizes

A frequent cause of #REF! is that the MATCH range is longer than the INDEX range. MATCH can return a position that is valid in its own range but too large for INDEX’s array. In the example below, the lookup range has five cells, while the return range has only four. If E2 contains P-105, MATCH returns 5; INDEX cannot retrieve the fifth item from B2:B5, so the formula returns #REF!.

With product codes in A2:A6, prices in B2:B5, and P-105 in E2, this formula produces #REF! because the match position is 5 but the return array contains only four rows:

=INDEX($B$2:$B$5,MATCH(E2,$A$2:$A$6,0))

Make both ranges start and end on corresponding rows. With the same layout but prices extended through B6, the corrected formula returns 44:

With product codes in A2:A6, prices in B2:B6, and P-105 in E2, use:

=INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0))

When troubleshooting, compare the range endpoints as well as the range sizes. A formula can use equally sized ranges that are offset from one another; it may return the wrong item rather than an error. The lookup and return ranges should generally correspond row by row.

Inspect INDEX row and column numbers

INDEX can return a value from a one-dimensional list or from a two-dimensional table. In a two-dimensional array, its row number must fit the array’s row count and its column number must fit the array’s column count. For instance, asking a five-row, two-column array for column 3 is out of bounds and can return #REF!. A negative row or column number is also invalid.

When the row or column number comes from a separate calculation, inspect that part of the formula too. A MATCH range that was expanded while the INDEX range stayed the same can produce an out-of-range position. A copied formula may also have shifted a reference if it was not anchored with dollar signs. Select the formula cell and review the displayed references in the formula bar to spot unexpected changes.

There is also a less common reference form of INDEX, which can take a list of separate areas and an area number. If the area number asks for an area that is not included, INDEX returns #REF!. With two areas A2:A6 and C2:C6, asking for area 3 is invalid:

With A2:A6 and C2:C6 as the two referenced areas, this formula requests a nonexistent third area:

=INDEX((A2:A6,C2:C6),2,1,3)

Most standard INDEX MATCH lookups use INDEX’s array form and do not need an area number. If your formula has four INDEX arguments or a parenthesized list of ranges, check that reference form specifically.

Look for broken or changed references

A formula may show #REF! because Excel no longer has a valid cell or range to refer to. This can happen after a referenced row, column, range, or worksheet is deleted. For example, a formula that depended on a source sheet may become invalid if that sheet is removed. Excel may also display #REF! directly inside the formula where a reference used to be.

Click the error cell and read the formula bar carefully. If you see #REF! as part of the formula itself, repair that reference by selecting the intended source range or typing a valid reference. If the formula looks intact, inspect the ranges and the result of MATCH. In Microsoft 365 for Windows, use Formulas > Evaluate Formula to step through the calculation. Formulas > Trace Precedents can also help show which cells feed into the formula.

Be cautious when editing or deleting source data. If you are rearranging a workbook, check formulas that depend on the affected sheet or range afterward. If the intended source data has genuinely been removed, restoring a valid reference may require recovering the data or pointing the formula to a replacement range; changing the formula alone cannot recreate missing values.

A practical troubleshooting sequence

Work through these checks in order, starting with the smallest and easiest-to-confirm issues:

  1. Click the formula cell and determine whether #REF! appears inside the formula or only as its calculated result.
  2. Check that INDEX points to the intended return range and MATCH points to the intended lookup range.
  3. Count the rows in both ranges. For a vertical lookup, make sure corresponding lookup and return ranges have matching row counts and alignment.
  4. Check MATCH’s third argument. Use 0 for an exact match unless you intentionally want an approximate match.
  5. Work out the position MATCH returns and confirm INDEX has a corresponding row or column.
  6. Look for deleted source rows, columns, ranges, or sheets, as well as references that shifted when the formula was copied.
  7. Use Formulas > Evaluate Formula if the cause is still unclear, then correct the underlying reference rather than hiding the error.

For the sample data, P-105 is the fifth item in A2:A6. If INDEX uses B2:B5, its array has only four items, so the problem is clear without changing the lookup code or the data. This position-based check is useful even when ranges contain hundreds of rows: MATCH’s result needs to fit inside the corresponding INDEX dimension.

Why IFERROR is not always the fix

IFERROR can replace an error with a message, but it does not repair the reference. If the formula should find a price and its range is too short, hiding the error could make a broken lookup look like an ordinary missing result. Use an error-handling wrapper only when that display is appropriate and you have already checked the underlying ranges.

With product codes in A2:A6, prices in B2:B6, and the lookup code in E2, this formula displays a clear message if the lookup calculation returns any error:

=IFERROR(INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0)),"Check lookup ranges")

Because IFERROR catches errors broadly, it can conceal problems other than a value not being found. IFNA catches only #N/A, so it will not hide a #REF!. If you want to handle a missing lookup value but still see a broken reference, IFNA is often the more targeted choice. In either case, fix any unexpected errors at their source.

INDEX MATCH compared with XLOOKUP

For a straightforward lookup, XLOOKUP can be easier to read because it takes the lookup range and return range as separate arguments. It is available in Microsoft 365 and Excel 2021 or later. With product codes in A2:A6, prices in B2:B6, and the lookup code in E2, this formula returns the matching price or the words “Not found” if there is no match:

=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,"Not found")

XLOOKUP reduces the need to count a MATCH position manually, but its lookup and return ranges still need to represent corresponding data. It is not available in some older Excel versions, where INDEX MATCH remains useful. If a workbook must work in older versions, confirm which functions those versions support before replacing existing formulas.

Frequently Asked Questions

Does MATCH return #REF! when it cannot find a value?
Usually no. MATCH with exact-match mode returns #N/A when it cannot find the lookup value. #REF! more often points to a broken reference or to INDEX requesting a position outside its array.

Why does INDEX MATCH work for some items but return #REF! for others?
One possible reason is that the position for some items exceeds the size of the INDEX range. For example, a five-cell lookup range can return position 5 even if the return range contains only four cells. Compare the range sizes and endpoints.

Can using 0 in MATCH cause #REF!?
In MATCH(lookup_value,lookup_array,0), 0 requests an exact match. It does not itself cause #REF!. It can help avoid approximate-match behavior, but an out-of-range INDEX position or broken reference must still be corrected.

How do I fix #REF! after deleting a worksheet?
Check whether the formula now contains #REF! in place of the deleted sheet reference. If the intended source exists elsewhere, edit the formula to point to the correct sheet and range. If the source data was deleted and is not available, restoring or replacing that data may be necessary.

Should I replace INDEX MATCH with XLOOKUP?
Consider XLOOKUP for readability if the workbook will be used in Microsoft 365 or Excel 2021 and later. Keep INDEX MATCH when compatibility with older Excel versions matters or when an existing formula suits the workbook. Either approach requires valid, correctly aligned source ranges.

Related

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.