한국어

#REF! 오류: Excel과 Google Sheets의 원인과 고치는 방법

#REF!는 참조가 잘못되었거나, 이미 값이 있는 칸에 쓰려 할 때 납니다. 이번 테스트에서 열이 4개인 범위에 5번째 열을 지정한 VLOOKUP, 배열 상수를 벗어난 INDEX, 없는 시트를 가리킨 INDIRECT, 시트 밖으로 나간 OFFSET, 이미 값이 있는 칸으로 넘치는 SPLIT은 모두 #REF!였습니다.

Excel 오류 수정 작성자 Oscar 읽는 시간 5분 게시일
목차

빠른 답변

#REF!는 참조가 잘못되었거나, 이미 값이 있는 칸에 쓰려 할 때 납니다. 이번 테스트에서 열이 4개인 범위에 5번째 열을 지정한 VLOOKUP, 배열 상수를 벗어난 INDEX, 없는 시트를 가리킨 INDIRECT, 시트 밖으로 나간 OFFSET, 이미 값이 있는 칸으로 넘치는 SPLIT은 모두 #REF!였습니다.

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

확인 사항

대조인 VLOOKUP("SKU-103",A2:D4,3,FALSE)는 0.55입니다. INDEX({10,20,30},1,4)와 INDEX({10,20,30},20)는 #REF!이지만, INDEX(A2:A4,20)는 #REF!가 아니라 #NUM!이었습니다. 열 번호 0인 VLOOKUP은 이전 테스트에서 #VALUE!였습니다. 키가 없으면 #REF!가 아니라 #N/A입니다.

주의점 및 호환성

SPLIT은 오른쪽 칸에 OCCUPIED와 ALSO가 있으면 #REF!를 반환하고 그 값은 덮어쓰지 않습니다. INDIRECT("NoSuchSheet!A1")와 OFFSET(A1,-2,0)도 #REF!입니다. 이번에는 열을 삭제하지 않았고, 없는 시트 이름이 실제로 확인한 가장 가까운 경우입니다.

영어 상세 예제, 수식 및 작성자 메모

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

스프레드시트 수식과 오류 해결을 소개합니다. 검증 환경과 미확인 사항은 각 글을 참고하세요.

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
다음 글: 시트 오류 및 동적 배열 #NUM! 오류: Excel과 Google Sheets의 원인과 고치는 방법
Excel 오류 수정

#VALUE! 오류: Excel과 Google Sheets의 원인과 해결

#VALUE!는 인수 형식이 잘못되었거나 허용되지 않는 인수일 때 나며, 조회 키가 없는 경우가 아닙니다. 이번 테스트에서 "SKU-101"+10, FIND 또는 SEARCH가 텍스트를 찾지 못할 때, VLOOKUP 열 번호 0은 모두 #VALUE!였습니다. 없는 키는 #N/A, VLOOKUP 열 번호가 너무 크면 #REF!입니다.

읽는 시간 7분
Excel 오류 수정

#DIV/0! 오류: Excel과 Google Sheets의 원인과 고치는 방법

#DIV/0!는 0으로 나누었다는 뜻입니다. 이번 Google Sheets에서 1/0, 10/0, 빈 칸으로 10을 나눔, 빈 문자열로 나눔, 0/0, 텍스트 0으로 1을 나눔, MOD(5,0), QUOTIENT(5,0)은 모두 #DIV/0!였습니다. 대조인 10/2는 5입니다.

읽는 시간 5분
Excel 오류 수정

#NAME? 오류: Excel과 Google Sheets의 원인과 고치는 방법

#NAME?는 수식의 함수 이름, 이름 있는 범위, 식별자를 인식하지 못할 때 납니다. 조회 실패인 #N/A도, 형식이 잘못된 #VALUE!도 아닙니다. 이번 Google Sheets 테스트에서 SUMM, XLLOOKUP, IFEROR, 따옴표 없는 hello, 없는 이름 범위는 모두 #NAME?이었습니다.

읽는 시간 5분