이 글의 목적은 엑셀 수식에서 INDIRECT 함수가 참조를 찾지 못해 #REF! 또는 빈 결과를 반환하는 다양한 원인을 체계적으로 진단하고, 재현 가능한 해결 절차와 안전한 대체 공식을 제시하여 현장에서 즉시 적용할 수 있도록 돕는 것이다.
1. INDIRECT 함수 핵심 동작 이해
INDIRECT(reference_text, [a1]) 함수는 텍스트로 주어진 주소(reference_text)를 실제 셀 참조로 변환하여 값을 반환하는 함수이다. A1 표기법을 기본으로 하며, 두 번째 인수 a1이 TRUE 또는 생략 시 A1 표기, FALSE 시 R1C1 표기법을 사용한다. INDIRECT는 휘발성(volatile) 함수로서 통합 문서의 어떤 변경에도 재계산되며, 외부 통합 문서가 닫혀 있으면 참조를 평가하지 못한다.
=INDIRECT("Sheet1!B3") ' A1 표기 =INDIRECT("R3C2", FALSE) ' R1C1 표기 주의 : INDIRECT는 닫힌 외부 통합 문서를 평가하지 못하다. 외부 파일을 참조해야 한다면 파일을 연 상태에서만 정상 동작한다.
2. “참조를 찾지 못함”이 발생하는 대표 원인
| 원인 | 증상 | 핵심 해결책 |
|---|---|---|
| 시트명·범위명에 공백/특수문자 포함 | #REF! 또는 빈 값 | 시트명을 작은따옴표로 감싼다(예: '2025 Q1'!). 작은따옴표 자체는 2개로 이스케이프한다. |
| a1 인수와 표기법 불일치 | 잘못된 주소로 평가 | A1 표기를 쓰면 a1=TRUE, R1C1이면 a1=FALSE로 명시한다. |
| 외부 통합 문서 닫힘 | #REF! | 대상 통합 문서를 연다. 구조를 바꾸어 INDIRECT 없이 참조하거나 다른 방법을 사용한다. |
| 구조적 참조/테이블 헤더 잘못 결합 | #REF! 또는 0 | INDEX/XMATCH로 테이블을 주소로 변환 후 ADDRESS→INDIRECT 순서 사용 또는 직접 INDEX로 대체한다. |
| 이름 정의 범위 스코프 충돌 | 예상과 다른 시트 참조 | 이름 관리자에서 통합 문서/시트 스코프를 정리한다. |
| 로케일(리스트 구분자) 차이 | 수식 구문 오류 | 시스템 구분자에 맞춰 콤마(,) 또는 세미콜론(;)을 사용한다. |
| 숫자처럼 보이는 텍스트 주소 | 빈 값 | TRIM, CLEAN, SUBSTITUTE로 숨은 문자 제거 후 결합한다. |
| 3D 참조 시도 | #REF! | INDIRECT는 3D 참조를 지원하지 않는다. SUMPRODUCT+INDEX 방식으로 대체한다. |
| 동적 배열 범위 표기(#) 오용 | #REF! 또는 단일 셀만 반환 | SPILL 범위를 명확히 지정하거나 TAKE/WRAPROWS 등 동적 배열 함수로 대체한다. |
| 시트 삭제/이동으로 실제 참조 소멸 | #REF! | 참조 대상이 존재하는지 점검하고, 존재하지 않을 때의 대체 경로를 IFERROR로 처리한다. |
3. 시나리오별 진단 절차 체크리스트
3.1 시트명·주소 결합 오류
사용 예: 시트명이 “2025 Q1”처럼 공백을 포함할 때이다. 작은따옴표로 감싸야 한다.
=INDIRECT("'2025 Q1'!B3") 시트명을 셀에서 가져오는 동적 결합일 때이다.
=INDIRECT("'" & A2 & "'!" & B2) ' A2=시트명, B2=주소("B3" 등) 주의 : 시트명에 작은따옴표가 포함된 드문 케이스는 작은따옴표를 두 번 연속으로 대체한다. 예: A2에 O'Brien이 있으면 SUBSTITUTE(A2,"'","''")로 이스케이프한다.
3.2 A1/R1C1 표기 혼동
다음은 R1C1 표기를 A1로 잘못 평가하는 전형적인 사례이다.
=INDIRECT("R3C2") ' 잘못. 기본은 A1 평가 =INDIRECT("R3C2", FALSE) ' 올바른 R1C1 평가 3.3 외부 통합 문서 참조 실패
외부 파일이 닫혀 있으면 INDIRECT는 참조를 만들지 못한다. 파일을 연 뒤 다음 형식을 쓴다.
=INDIRECT("'[매출_2025.xlsx]" & A2 & "'!" & B2) ' A2=시트명, B2=주소("C5" 등). 외부 파일은 열린 상태여야 한다. 주의 : 외부 파일 경로까지 필요한 경우 전체 경로를 포함하되, 경로·파일명·시트명 모두 작은따옴표로 감싸는 패턴을 유지한다.
3.4 이름 정의 스코프 충돌
같은 이름이 통합 문서 스코프와 시트 스코프에 중복으로 존재하면 INDIRECT가 의도와 다르게 해석할 수 있다. 이름 관리자에서 중복을 제거하거나 명시적 시트 스코프를 준다.
=INDIRECT("Sheet1!" & "매출범위") ' 스코프를 시트로 강제 3.5 로케일별 인수 구분자
일부 환경에서 함수 인수 구분자는 세미콜론(;)이다. 구문 오류가 발생하면 시스템의 “목록 구분 기호” 설정에 맞춘다.
=INDIRECT("Sheet1!B3"; FALSE) ' 세미콜론 사용 환경 예시 3.6 숨은 문자·가변 공백 제거
웹·ERP에서 복사한 주소에는 보이지 않는 문자가 섞여 있다. 다음 정규화 패턴을 적용한다.
=LET( raw, A2, addr, TRIM(SUBSTITUTE(CLEAN(raw),CHAR(160)," ")), INDIRECT(addr) ) 3.7 3D 참조 요구 사항
“Sheet1:Sheet4!B3” 형태의 3D 참조는 INDIRECT가 지원하지 않는다. 다음과 같이 INDEX를 사용하여 대체한다.
=SUMPRODUCT( N(INDIRECT("'" & INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4"},0) & "'!B3")) ) 주의 : 위 수식은 대상 시트들이 모두 존재하고, 통합 문서 내부 시트만 대상으로 할 때 안정적으로 동작하다.
4. 재현 가능한 해결 패턴 모음
4.1 안전한 주소 생성(주소 검증 포함)
=LET( s, A2, ' 시트명 a, B2, ' 주소 텍스트("C5" 등) s2, "'" & SUBSTITUTE(s,"'","''") & "'!", ' 이스케이프 포함 ref, s2 & a, IFERROR(INDIRECT(ref), "주소 없음") ) 4.2 R1C1 주소를 동적으로 만들 때
=LET( r, ROWS(A$1:A1)+2, ' 예: 3행부터 증가 c, 2, INDIRECT("R"&r&"C"&c, FALSE) ) 4.3 테이블(Structured Reference)과 병행 사용
테이블의 특정 열에서 행 번호로 값을 가져오고 싶을 때 INDEX로 대체하면 INDIRECT를 피하면서 안정성을 높일 수 있다.
=INDEX(Tbl[매출], MATCH(E2, Tbl[월], 0)) 테이블 내 주소 텍스트가 필요한 상황이면 ADDRESS로 절대주소를 만든 뒤 INDIRECT로 평가한다.
=LET( r, MATCH(E2, Tbl[월], 0), c, MATCH("매출", Tbl[#Headers], 0), adr, ADDRESS(ROW(INDEX(Tbl[#All], r, c)), COLUMN(INDEX(Tbl[#All], r, c))), INDIRECT(adr) ) 4.4 외부 통합 문서 대체 전략
외부 파일이 항상 열려 있지 않다면 INDIRECT를 구조적으로 제거하는 것이 장기적으로 안전하다.
- 필요 데이터만 링크 붙여넣기(값/수식) 또는 Power Query로 가져오다.
- INDEX/XMATCH로 내부 범위를 참조하고, 외부 파일은 주기적으로 갱신한다.
- 필요 시 VBA로 갱신 루틴을 구현한다.
4.5 동적 배열과의 안전 결합
스필 범위를 주소로 만들고자 할 때이다.
=LET( spill, F2#, top, ADDRESS(ROW(INDEX(spill,1,1)), COLUMN(INDEX(spill,1,1))), bot, ADDRESS(ROW(INDEX(spill,ROWS(spill),COLUMNS(spill))), COLUMN(INDEX(spill,ROWS(spill),COLUMNS(spill)))), rng, top & ":" & bot, INDIRECT(rng) ) 5. 빠른 원인 판별 도구
5.1 참조 유효성 검사
=ISREF(INDIRECT(A2)) ' TRUE면 참조 생성 성공 =IFERROR(1*N(INDIRECT(A2)),"X") ' 숫자 강제→오류 탐지 5.2 존재하지 않는 시트 탐지
=LET( s, A2, test, T(INDIRECT("'" & SUBSTITUTE(s,"'","''") & "'!A1")), IF(test="", "시트 존재", "시트 존재") ) 위 예시는 INDIRECT가 오류를 내면 전체 수식이 오류가 되므로 IFERROR로 감싸는 것이 실무적이다.
=IFERROR(T(INDIRECT("'" & SUBSTITUTE(A2,"'","''") & "'!A1")), "시트 없음") 6. 자주 쓰는 안정화 템플릿
6.1 시트·셀 주소 분리형
=LET( s, A2, a, B2, ref, "'" & SUBSTITUTE(s,"'","''") & "'!" & a, IFERROR(INDIRECT(ref), NA()) ) 6.2 범위 주소(시작/끝) 결합형
=LET( s, A2, s2, "'" & SUBSTITUTE(s,"'","''") & "'!", start, C2, end_, D2, ref, s2 & start & ":" & end_, IFERROR(INDIRECT(ref), "") ) 6.3 A1↔R1C1 변환 템플릿
=LET( r, 5, c, 3, adr, ADDRESS(r, c, 1), ' A1 주소 INDIRECT(adr) ' R1C1 필요 시 INDIRECT("R"&r&"C"&c,FALSE) ) 7. 성능과 유지보수 고려
- 휘발성 함수 최소화: 대용량 시트에서는 INDIRECT 호출 수를 줄이고 INDEX/XMATCH로 치환한다.
- 주소 정규화 레이어: LET 블록에서 주소를 정리한 뒤 마지막 한 번만 INDIRECT로 평가한다.
- 오류 처리 일관화: IFERROR로 사용자 친화적 메시지를 제공한다.
- 참조 안정성: 시트명 변경 가능성이 있으면 이름 정의를 중앙화하고, 시트명은 목록에서 선택하도록 데이터 유효성 검사로 제한한다.
8. 실제 문제별 해결 예시
문제 A: “2025-Q1 매출” 시트를 드롭다운으로 선택하면 #REF!
원인: 공백과 하이픈 포함 시트명인데 작은따옴표 없이 결합했기 때문이다.
=INDIRECT(A2 & "!B3") ' 잘못된 예 =INDIRECT("'" & A2 & "'!B3") ' 올바른 예 문제 B: ERP에서 복사한 주소가 간헐적 실패
원인: CHAR(160) 등의 비분리 공백 포함이다.
=LET(raw,A2, addr, TRIM(SUBSTITUTE(CLEAN(raw),CHAR(160)," ")), INDIRECT(addr)) 문제 C: 외부 파일 닫힘으로 인한 실패
해결: 외부 파일을 연다. 또는 구조를 변경하여 INDIRECT를 제거한다.
' 대체안: Power Query 또는 값 스냅샷으로 외부 데이터를 로드 문제 D: R1C1 오해
=INDIRECT("R10C5", FALSE) ' R1C1로 정확히 지정 문제 E: 테이블 열 동적 선택
목표 열 제목을 셀에서 선택할 때는 INDEX/XMATCH 조합이 더 안전하다.
=INDEX(Tbl[#All], XMATCH(E2, Tbl[월], 0), XMATCH(F2, Tbl[#Headers], 0)) 9. 대체 공식 전략: INDIRECT 없이 동적 참조 구현
9.1 INDEX로 동적 셀 참조
=INDEX(INDIRECT("'" & A2 & "'!1:1048576"), ROW(B3), COLUMN(B3)) ' 또는 시트가 고정이면 =INDEX(Sheet1!1:1048576, ROW(B3), COLUMN(B3)) 시트가 여러 개인 경우에는 CHOOSECOLS/TAKE 등과 결합하거나, 구조를 표준화하여 INDEX로 접근 범위를 고정한다.
9.2 XLOOKUP/XMATCH 기반
=XLOOKUP(H2, INDEX(INDIRECT("'" & A2 & "'!A:A"), ), INDEX(INDIRECT("'" & A2 & "'!C:C"), )) 가능하면 동일 시트 내 표로 변환한 후 XLOOKUP 하나로 해결하는 구조가 유지보수에 유리하다.
10. 품질 관리 체크리스트
- 주소 텍스트에 불필요한 공백·제어문자가 없는지 확인한다.
- 시트명에 공백·특수문자가 있으면 작은따옴표로 감싼다.
- a1 인수를 표기법에 맞게 TRUE/FALSE로 명시한다.
- 외부 통합 문서를 사용할 경우 항상 열린 상태에서 테스트한다.
- 이름 정의의 스코프가 의도한 대로 설정되어 있는지 확인한다.
- 대량 데이터에서는 INDIRECT 호출 수를 줄이고 INDEX/XMATCH로 치환한다.
- 모든 동적 참조는 IFERROR로 오류 메시지를 일관되게 처리한다.
11. 실무용 스니펫 모음
11.1 안전 결합 함수(LAMBDA)
=LAMBDA(sht, adr, LET( s2, "'" & SUBSTITUTE(sht,"'","''") & "'!", IFERROR(INDIRECT(s2 & adr), NA()) ) ) ' 이름 관리자에 SafeRef로 저장 후 =SafeRef(A2,B2) 형태로 사용 11.2 시트 존재 여부 검사
=IFERROR(NOT(ISERROR(T(INDIRECT("'" & SUBSTITUTE(A2,"'","''") & "'!A1")))), FALSE) 11.3 주소 정규화 유틸리티
=LAMBDA(t, TRIM(SUBSTITUTE(CLEAN(t),CHAR(160)," "))) 12. 자주 하는 실수 요약표
| 실수 | 잘못된 예 | 올바른 예 |
|---|---|---|
| 공백 있는 시트명에 따옴표 미사용 | =INDIRECT( A2 & "!B3") | =INDIRECT("'" & A2 & "'!B3") |
| R1C1인데 a1 생략 | =INDIRECT("R5C2") | =INDIRECT("R5C2", FALSE) |
| 외부 파일 닫힘 | =INDIRECT("'[외부.xlsx]S'!A1") | 외부 파일을 연 상태에서 사용 |
| 숨은 문자 미제거 | =INDIRECT(A2) | =INDIRECT(TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160)," "))) |
| 3D 참조 오용 | =INDIRECT("Sheet1:Sheet4!B3") | =SUMPRODUCT(N(INDIRECT("'" & INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4"},0) & "'!B3"))) |
FAQ
INDIRECT로 외부 통합 문서를 참조할 수 없나?
외부 통합 문서가 닫혀 있으면 평가하지 못하다. 파일을 열어야 하며, 장기적으로는 Power Query·값 스냅샷·INDEX 기반 구조로 대체하는 것이 안정적이다.
함수 인수 구분자가 콤마인지 세미콜론인지 헷갈린다.
운영체제의 목록 구분 기호 설정을 따른다. 구문 오류가 발생하면 반대 구분자로 바꾸어 테스트한다.
시트명을 드롭다운에서 선택하면 가끔 오류가 난다.
선택 값에 숨은 문자가 포함될 수 있다. CLEAN, TRIM, SUBSTITUTE로 정규화한 뒤 결합한다.
동적 배열과 INDIRECT를 같이 쓰면 느려진다.
휘발성 함수가 스필 범위 전체를 재계산하기 때문이다. INDEX/XMATCH로 대체하거나 LET으로 주소 생성 횟수를 최소화한다.
R1C1 표기법을 강제로 쓰고 싶다.
INDIRECT의 두 번째 인수에 FALSE를 준다. 예: =INDIRECT("R10C5", FALSE) 형태이다.