INDEX MATCH #REF! Error: How to Find and Fix the Cause

A #REF! error in an Excel INDEX MATCH formula means that part of the formula points to a cell or range Excel cannot use, or asks INDEX for a position outside its range. Check the INDEX return range and the MATCH lookup range first; an ordinary value that is not found usually produces #N/A, not #REF!.

What #REF! Means in INDEX MATCH

Excel uses #REF! to signal an invalid cell reference. In an INDEX MATCH lookup, the error may come from INDEX, from MATCH, or from a reference elsewhere in the formula. It does not, by itself, mean that the lookup value is missing. That distinction matters: replacing every error with blank text can conceal a broken range rather than repair it.

A typical formula asks MATCH to find a value in one range, then passes the resulting position to INDEX to return a value from another range. If the lookup value exists but the return reference was deleted, or if INDEX is asked for the seventh item in a six-item range, the formula can return #REF!. If the lookup value simply does not exist in an otherwise valid lookup range, MATCH normally returns #N/A, which then passes through the formula.

Excel often makes the broken reference visible in the formula itself by displaying #REF! where a cell or range address used to be. This can happen after deleting referenced cells or a worksheet. A formula may also look syntactically intact while its INDEX row or column position exceeds the dimensions of its array.

How INDEX MATCH Works

INDEX returns a value from a position in a range. MATCH finds the position of a lookup value in a range. In the common exact-match pattern, the position found by MATCH tells INDEX which item to return. The lookup and return ranges should normally contain the same number of rows, in corresponding order.

With IDs in A2:A6, names in B2:B6, and the ID to find in E2, the formula below returns the corresponding name. If E2 contains 104 and that ID is on the fourth row of the lookup range, the result is the fourth item in the return range, Mira.

With IDs in A2:A6, names in B2:B6, and the lookup ID in E2, use =INDEX(B2:B6,MATCH(E2,A2:A6,0)).

The final 0 tells MATCH to look for an exact match. It is not an instruction to return the first row or to ignore errors. INDEX uses the position that MATCH returns, counted from the beginning of the specified range, not the worksheet row number. For example, the first item in A2:A6 has position 1 even though it is on worksheet row 2.

Common Causes of a #REF! Error

A referenced range was deleted. If a formula refers to cells that are removed, Excel may replace that part of the reference with #REF!. Deleting a worksheet used by the formula can cause the same kind of problem.
INDEX is given an out-of-range position. A row number greater than the number of rows in the INDEX array, or a column number greater than its number of columns, is invalid. For a one-column return range, a position of 6 cannot identify an item in a five-cell range.
The formula was edited or copied incorrectly. A range may have been shortened, a reference may have been overwritten, or relative references may have shifted when the formula was filled down or across.
A reference-form INDEX uses an invalid area number. INDEX can work with one or more reference areas. If its area_num asks for an area that is not included, Excel can return #REF!. This is less common than a deleted range or an invalid position.
A referenced sheet or external source is no longer available as expected. Renamed or deleted sheets, changed workbook links, and edits to formulas that include sheet references can leave a formula with an invalid reference. A closed source workbook alone does not automatically mean every external reference is broken, so inspect the actual formula before assuming that is the cause.

For a simple position example, with values in B2:B6, the range contains five items. The formula below asks for item 6, which does not exist, so INDEX returns #REF!.

With the five return values in B2:B6, use =INDEX(B2:B6,6).

How to Diagnose the Formula

Start with the formula bar. Select the cell showing the error and read the complete formula rather than relying on the shortened display in the grid. Look for a visible #REF! token, a deleted sheet name, or a range that no longer covers the intended data. If the formula is long, copy it into the formula bar or inspect it in smaller parts.

On Excel for Microsoft 365 on Windows, select the formula cell and choose Formulas > Evaluate Formula to step through how Excel calculates the expression. Formulas > Error Checking can also help identify a cell containing an error. The formula uses the same calculation rules in Excel for Mac and Excel for the web, although the ribbon layout and availability of individual troubleshooting commands may differ.

Check each component separately: confirm that the MATCH lookup range exists, then verify that the INDEX return range exists, and finally confirm that the position produced by MATCH is valid for the return range. Make sure both ranges start and end on corresponding records. If one range has five items and the other has four, the formula may return an incorrect result for some positions; if the position exceeds the return range, INDEX can return #REF!.

When the formula contains no visible broken reference, test whether MATCH is finding a position at all. With the IDs in A2:A6 and the lookup value in E2, use =MATCH(E2,A2:A6,0). A numeric result is the position within A2:A6. #N/A means no exact match was found; #REF! suggests the reference supplied to MATCH is invalid. This test helps separate a lookup-data issue from a broken range.

How to Fix It

Repair the underlying reference rather than hiding the error. If Excel shows #REF! inside the formula, replace that fragment with the correct range or sheet reference. If the source cells were deleted, restore the cells or edit the formula to point to the intended surviving data. When the correct source is unclear, compare the formula with a working formula in the same column and confirm the intended table boundaries before changing anything.

If INDEX is receiving a position that exceeds its range, check how MATCH is being used and ensure that the INDEX range contains the corresponding records. With IDs in A2:A6, names in B2:B6, and the lookup value in E2, an aligned exact-match formula is =INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0)). If E2 is 104 and that ID is fourth in A2:A6, this returns the fourth name, Mira. The dollar signs keep the data ranges fixed when the formula is copied; E2 remains relative so it can change to E3, E4, and so on.

Do not fix a range mismatch by blindly enlarging one side. The extra rows must represent corresponding records in the same order. Otherwise, the formula may stop showing #REF! but return a name from the wrong record. Check the first, last, and a few middle entries after making a repair.

Distinguishing #REF! from #N/A and Other Errors

#REF! means a reference or requested position is invalid. #N/A commonly means MATCH could not find the requested value using the chosen match setting. For example, a number stored as text may not match a numeric lookup value, or a spelling difference may prevent an exact match. Those issues deserve attention, but they do not make a valid range into #REF!.

#VALUE! usually points to an argument or value-type problem, while #NAME? can indicate text Excel does not recognize as a function or defined name. The error alone is not a complete diagnosis, but identifying its type narrows the search. In particular, wrapping INDEX MATCH in IFERROR does not repair any of these underlying causes.

Preventing Future Reference Errors

Use consistent, aligned ranges and make formulas easier to audit. When filling a formula down, lock ranges that should stay fixed and leave the lookup cell relative, as in the example above. Convert a data range into an Excel table when the source grows regularly; structured references can make formulas easier to follow and often adjust as table rows are added. Still, review formulas after deleting columns, sheets, or source data.

Before making major structural edits, check whether important formulas refer to the cells or sheets being changed. You can use Find to search formulas for a sheet name or a distinctive range reference, and save a copy before extensive edits. For shared workbooks, avoid deleting source tabs simply because they appear unused until dependent formulas have been checked.

When to Consider Another Lookup Method

INDEX MATCH remains useful when a lookup needs flexibility, such as returning a value from a column to the left of the lookup column. XLOOKUP is a more direct option in Microsoft 365 and Excel 2021 or later, and can make a basic lookup easier to read. Changing functions does not automatically fix a broken source reference, however; a deleted range or missing sheet still needs to be corrected.

For example, with IDs in A2:A6, names in B2:B6, and the lookup ID in E2, the XLOOKUP version is =XLOOKUP(E2,A2:A6,B2:B6). For E2 equal to 104, it returns Mira if that is the corresponding name. XLOOKUP is not available in some older Excel versions, so use a method supported by the workbook’s users. In Excel for the web and Mac, function availability depends on the Excel version and update channel, as well.

Frequently Asked Questions

Does INDEX MATCH return #REF! when it cannot find a match?
No. With an exact-match lookup, MATCH normally returns #N/A when it cannot find the value. #REF! points instead to an invalid reference or an INDEX position outside the available range.

Why does my formula show #REF! inside the formula bar?
Excel often inserts #REF! into a formula when a referenced cell, range, or sheet has been deleted or otherwise made unavailable. Replace the broken part with the intended valid reference.

Can different-sized INDEX and MATCH ranges cause #REF!?
They can lead to an invalid INDEX position if MATCH returns a position that the smaller return range does not contain. Even when no error appears, mismatched ranges can pair the wrong lookup item with a result, so align their dimensions and order.

Will IFERROR fix an INDEX MATCH #REF! error?
IFERROR can display a chosen value instead of an error, but it does not restore a deleted reference or correct an invalid INDEX position. Repair the cause first; use error handling only when a substitute display is appropriate.

Does INDEX MATCH work the same way on Mac?
The core formula syntax and lookup behavior are the same in current Excel for Mac and Excel for Microsoft 365 on Windows. Some ribbon commands and troubleshooting options can be arranged differently, but the reference and range checks still apply.

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.