#REF! 错误：Excel 与 Google Sheets 的原因和修法

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.

Related tutorials

• #VALUE! Error in Excel and Google Sheets: Causes and Fixes
• #DIV/0! Error in Excel and Google Sheets: Causes and Fixes
• #NAME? Error in Excel and Google Sheets: Causes and Fixes
• Fix “You Need to Connect These Sheets” IMPORTRANGE #REF! Error
• Excel & Google Sheets Lookup Errors: The Complete Troubleshooting Guide
• Split Text by Delimiter in Excel and Google Sheets

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

• Microsoft Support: How to correct a #REF! error: https://support.microsoft.com/en-us/office/how-to-correct-a-ref-error-822c8e46-e610-4d02-bf29-ec4b8c5ff4be
• Microsoft Support: VLOOKUP function: https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
• Microsoft Support: INDEX function: https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd
• Google Docs Editors Help: INDIRECT: https://support.google.com/docs/answer/3093375
• Google Docs Editors Help: SPLIT: https://support.google.com/docs/answer/3094136