English

#REF! Error in Excel and Google Sheets: Causes and Fixes

#REF! means a reference is invalid. Tested: a VLOOKUP column too far right, INDEX off an array, INDIRECT, OFFSET off the grid, and a blocked SPLIT.

Excel Error fix By Oscar 5 min read Published
On this page

Which versions this applies to

The results below were measured in Google Sheets on November 12, 2026. Microsoft documents #REF! in Excel when a formula refers to a cell that is not valid, including after a row or column is deleted. I did not delete a column in this run, and I did not re-test these rows in desktop Excel today.

Cause 1: VLOOKUP asked for a column that is not in the range

The test table A2:D4 has four columns (SKU, Product, Price, Warehouse). SKU-103 is in that table, with price 0.55:

=VLOOKUP("SKU-103",A2:D4,5,FALSE)    → #REF!
=VLOOKUP("SKU-103",A2:D4,3,FALSE)    → 0.55

Column 5 is one past the right edge of A2:D4, so the lookup cannot return a cell. The same key with column 3 (Price) returned 0.55. If you add or remove a column and forget to update the index, this is the error you get — not #N/A.

A column index of 0 is a different error. In an earlier test in this same file, VLOOKUP(...,0) returned #VALUE!. See #VALUE! Error in Excel and Google Sheets.

Cause 2: INDEX walked off an array constant — but not always off a range

These two look similar and are not:

=INDEX({10,20,30},1,4)    → #REF!
=INDEX({10,20,30},20)     → #REF!
=INDEX(A2:A4,20)          → #NUM!

INDEX into the array {10,20,30} returned #REF! when the column or the row was past the array. INDEX into the worksheet range A2:A4 with row 20 returned #NUM!, not #REF!. If you are troubleshooting a long INDEX/MATCH formula, look at whether the first argument is a range or an array constant before you assume the error type.

Cause 3: INDIRECT or OFFSET points somewhere that does not exist

=INDIRECT("NoSuchSheet!A1")    → #REF!
=OFFSET(A1,-2,0)               → #REF!

There is no sheet named NoSuchSheet in this file, so INDIRECT cannot build a reference. OFFSET from A1 up two rows would land above the sheet, and that was also #REF!.

I did not delete a column as part of this test. Microsoft describes #REF! in Excel when a formula still refers to a cell that has been deleted. The closest case I actually ran is the missing-sheet INDIRECT above. A related, Sheets-specific #REF! — IMPORTRANGE before you grant access — is covered in Fix “You Need to Connect These Sheets”.

Cause 4: SPLIT tried to overwrite cells that already have values

A15 held the text one,two,three. I entered =SPLIT(A15,",") in A16, with B16 already holding OCCUPIED and C16 holding ALSO:

=SPLIT(A15,",")    → #REF!

B16 and C16 were left as they were. SPLIT needs a clear spill area to the right. The same kind of collision is why Array Result Was Not Expanded appears on other spilling formulas; for SPLIT, the error in this file was #REF!.

A working SPLIT with empty neighbors is in Split Text by Delimiter.

#REF! is not a missing lookup

When the key is not in the table, an exact VLOOKUP or XLOOKUP returns #N/A, not #REF!. The control lookup for SKU-103 with a legal column index returned a price, so this sheet’s #REF! rows were all invalid references, not missing SKUs. The broader lookup-error map is in Excel & Google Sheets Lookup Errors: The Complete Troubleshooting Guide.

Excel and Google Sheets differences

Microsoft documents #REF! for invalid or deleted references in Excel, and for a VLOOKUP column index greater than the width of the table. I am quoting those docs, not an Excel workbook I opened today. The INDEX range-vs-array split (#NUM! vs #REF!) is a Sheets result from this file; I would not assume Excel uses the same pair of errors without testing it.

Common mistakes

  • Counting columns from the left of the sheet instead of from the left of the VLOOKUP range. Column 5 was #REF! in a 4-column range even though the key existed.
  • Treating INDEX(A2:A4,20) as #REF!. It was #NUM! here. INDEX({10,20,30},20) was #REF!.
  • Leaving values in the cells SPLIT needs to spill into. That was #REF!, and the occupied cells were not overwritten.
  • Assuming #REF! means the lookup key is missing. A missing key is #N/A.

FAQ

Why does VLOOKUP return #REF! when I can see the value in the table?

The key can be there and the column index still be illegal. VLOOKUP("SKU-103",A2:D4,5,FALSE) returned #REF! because the range is only four columns wide. VLOOKUP("SKU-103",A2:D4,3,FALSE) returned 0.55.

Why did INDEX return #NUM! instead of #REF!?

INDEX(A2:A4,20) was #NUM!. INDEX({10,20,30},20) and INDEX({10,20,30},1,4) were #REF!. In this file, a row number past the end of a worksheet range was #NUM!; the same idea against an array constant was #REF!.

I did not delete anything. Why is INDIRECT showing #REF!?

The sheet name inside the string has to exist. INDIRECT("NoSuchSheet!A1") returned #REF! in a file that has no sheet by that name. Check typo’d tab names and quoted strings that assemble the reference.

Why did SPLIT return #REF!?

The spill area was not empty. With OCCUPIED and ALSO already in the two cells to the right, SPLIT returned #REF! and left those cells alone. Clear the cells, or move the formula.

Is #REF! the same as the IMPORTRANGE connect error?

It is the same error value, but the usual fix is different. IMPORTRANGE needs access to the other file, which is a one-time connect step, not a repaired column index. See the IMPORTRANGE article.

Live-test findings

Google Sheets, November 12, 2026. VLOOKUP("SKU-103",A2:D4,5,FALSE) #REF!; VLOOKUP("SKU-103",A2:D4,3,FALSE) 0.55. INDEX({10,20,30},1,4) and INDEX({10,20,30},20) #REF!; INDEX(A2:A4,20) #NUM!. INDIRECT("NoSuchSheet!A1") #REF!. OFFSET(A1,-2,0) #REF!. SPLIT of one,two,three into a row whose next two cells already held OCCUPIED and ALSO returned #REF! and left those cells unchanged.

Sources

Oscar

Spreadsheet formulas and troubleshooting. See each article for its actual verification environment and pending author checks.

Tested in Google Sheets, November 12, 2026. Excel version notes follow Microsoft documentation and were not re-tested in a desktop Excel workbook on this date..

#ref-error#vlookup#index#split#errors
Next in Sheets Errors & Dynamic Arrays #NUM! Error in Excel and Google Sheets: Causes and Fixes