엑셀 IFERROR가 작동하지 않을 때 해결법: 잘못된 값을 제대로 무시하는 공식 설계 가이드

Contents

1. 문제 정의: IFERROR는 “오류”만 처리한다
2. 대표 증상과 원인 매핑
3. 올바른 진단 절차
4. 상황별 패턴 레시피
   1. 조회 결과가 0 또는 빈 문자열일 때
   2. 분모가 0 또는 텍스트 “0”일 때
   3. 평균·합계에서 비정상 값 제외
   4. #N/A만 다른 오류와 구분 처리
   5. TRIM/CLEAN 후에도 남는 비가시 문자 제거
   6. 배열 전역 IFERROR의 남용 방지
5. 실무형 템플릿
   1. 조회-검증-대체 3단계
   2. 수치 변환 파이프라인
   3. 집계 전 유효 데이터 세척
6. IFERROR가 진짜 필요한 자리와 아닌 자리
7. 디버깅 체크리스트
8. 자주 쓰는 조각 코드
9. 동적 배열과 LET/LAMBDA로 유지보수성 향상
10. 사례 연구: 혼합 데이터 열의 평균 왜곡
11. 피벗·차트 입력값 정화
12. 업무 규칙 문문화 예시
13. FAQ

이 글의 목적은 엑셀에서 IFERROR 함수가 기대대로 “잘못된 값”을 건너뛰지 못할 때의 원인 진단과 대체 공식을 체계적으로 제시하여 현장에서 즉시 적용 가능한 해결책을 제공하는 것이다.

1. 문제 정의: IFERROR는 “오류”만 처리한다

IFERROR는 수식 결과가 오류일 때만 대체값을 반환하는 함수이다. 즉, #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL! 등 엑셀의 오류 유형에만 반응한다. 따라서 값이 “잘못되었다”는 의미가 사용자의 업무 규칙상 부적합함을 뜻하는 경우(예: 0, 빈 문자열 "", 공백, 임계값 미달, 텍스트-숫자 혼합 등)는 오류가 아니므로 IFERROR로는 걸러지지 않는다.

주의 : “잘못된 값”과 “오류 값”을 구분해야 한다. 업무 규칙 위반값은 오류가 아니므로 IFERROR가 무시하지 않는다.

2. 대표 증상과 원인 매핑

증상	근본 원인	핵심 해결 전략
VLOOKUP/XLOOKUP 결과가 0 또는 빈 문자열로 표시되는데 IFERROR가 작동하지 않음	0과 빈 문자열은 오류가 아님	업무 규칙을 명시한 IF, LET 조합으로 0·빈 문자열을 별도 처리하거나 XLOOKUP의 if_not_found와 혼동하지 않도록 분리 설계
분모가 0이 아닌데도 결과가 기대와 다름	텍스트 “0” 또는 공백이 숫자 비교를 교란	VALUE·이중단항(--)로 수치화, N 함수로 논리→숫자 변환
AVERAGE, SUMIFS 등에서 일부 비정상 값이 포함되어 평균/합계가 왜곡	비정상 값이 오류가 아니어서 포함됨	FILTER로 유효값만 선별 후 집계, 또는 AGGREGATE로 오류/숨김값 제외
TRIM/CLEAN 이후에도 IFERROR가 작동 불일치	비가시 문자, 유니코드 공백, 텍스트 숫자	SUBSTITUTE·UNICHAR 패턴 정리 후 VALUE 적용
배열 수식에서 일부 항목만 잘못되는데 IFERROR가 전체를 덮어씀	IFERROR를 배열 외곽에 1회 래핑	항목별 조건 분기 사용(요소 단위 IF), 동적 배열 함수와 조합
ISERROR/IFERROR 혼용 시 누락	특정 오류만 골라야 하는데 전부 포괄	IFNA·ISNA로 #N/A만 선택 처리

3. 올바른 진단 절차

1. 오류인지 비오류인지 판정을 먼저 한다. 셀을 선택하고 F2→Enter로 재계산, 수식 탭 > 수식 평가로 단계별 평가를 진행한다.
2. 자료형 검사를 수행한다. =ISTEXT(A1), =ISNUMBER(A1), =LEN(A1), =CODE(MID(A1,1,1))로 텍스트·숫자 여부와 비가시 문자를 확인한다.
3. 업무 규칙을 식으로 명문화한다. “0은 오류로 간주” 같은 규칙을 IF 조건으로 선언한다.
4. 배열 경계를 점검한다. 동적 배열 결과에 IFERROR를 한 번만 씌우면 전체가 같은 대체값으로 바뀔 수 있다.

4. 상황별 패턴 레시피

4.1 조회 결과가 0 또는 빈 문자열일 때

0과 빈 문자열은 오류가 아니므로 IFERROR가 반응하지 않는다. 규칙을 명시한다.

=LET( r, XLOOKUP(E2, A:A, B:B, "#N/A"),  IF(r="#N/A", "미존재", IF((r=0)+(r=""), "무효", r)) ) 

XLOOKUP의 if_not_found는 “검색 실패”에만 적용되므로 반환값이 0·빈문자일 때는 별도 IF로 분기한다.

4.2 분모가 0 또는 텍스트 “0”일 때

=LET( num, A2, den, --B2,  IF(OR(den=0, ISBLANK(B2)), "무효", num/den) ) 

텍스트 “0”을 --로 강제 수치화하여 0 판정이 정확해지도록 한다.

4.3 평균·합계에서 비정상 값 제외

동적 배열 기능이 있는 경우 유효값만 필터링한다.

=LET( v, A2:A100, valid, FILTER(v, (v<>"")*(ISNUMBER(v))*(v>=0)), IF(COUNTA(valid)=0, "유효 데이터 없음", AVERAGE(valid)) ) 

동적 배열 기능이 없는 환경에서는 AGGREGATE를 사용한다.

=AGGREGATE(1, 6, A2:A100)  

4.4 #N/A만 다른 오류와 구분 처리

=IFNA(MATCH(E2, A:A, 0), "미존재") 

IFNA는 #N/A만 잡는다. 다른 오류는 별도 분기한다.

=LET( m, MATCH(E2, A:A, 0), IF(ISNA(m), "미존재", IF(ISNUMBER(m), m, "기타 오류")) ) 

4.5 TRIM/CLEAN 후에도 남는 비가시 문자 제거

유니코드 공백(예: U+00A0)은 TRIM/CLEAN으로 제거되지 않을 수 있다.

=LET( t, SUBSTITUTE(A2, UNICHAR(160), ""), VALUE(TRIM(CLEAN(t))) ) 

4.6 배열 전역 IFERROR의 남용 방지

아래처럼 전체를 한 번에 감싸면 하나의 오류가 전체 결과를 덮어쓴다.

=IFERROR(A2:A10/B2:B10, "무효")  

항목별로 조건을 건다.

=LET( n, A2:A10, d, B2:B10*1, IF((d=0)+(ISBLANK(d)), "무효", n/d) ) 

5. 실무형 템플릿

5.1 조회-검증-대체 3단계

=LET( key, E2, found, XLOOKUP(key, A:A, B:B, "#N/A"), check, IF(found="#N/A", "미존재", IF(OR(found="", found=0, NOT(ISNUMBER(found))), "무효", "정상")), IF(check="정상", found, check) ) 

5.2 수치 변환 파이프라인

=LET( raw, A2, t1, SUBSTITUTE(raw, UNICHAR(160), ""), t2, TRIM(CLEAN(t1)), num, IFERROR(VALUE(t2), NA()), IFNA(num, "무효") ) 

5.3 집계 전 유효 데이터 세척

=LET( v, A2:A2000, u, FILTER(v, (v<>"")*(ISNUMBER(v))*(v<1E12)), IF(COUNTA(u)=0, "유효 데이터 없음", SUM(u)) ) 

6. IFERROR가 진짜 필요한 자리와 아닌 자리

상황	권장	비고
외부 참조 끊김으로 #REF! 발생	IFERROR로 임시 대체값 반환	근본 해결은 참조 복구이다.
검색 실패 시 메시지 표기	IFNA 또는 XLOOKUP의 if_not_found	#N/A만 표적 처리한다.
0·빈문자 등 규칙 위반값 제외	IF·FILTER·LET로 유효성 판단	오류가 아니므로 IFERROR 대상이 아니다.
배열 전역 오류 차단	항목별 조건 분기	전역 IFERROR는 결과 손실 위험이 있다.

7. 디버깅 체크리스트

1. 수식 평가로 중간값을 확인한다.
2. 자료형 테스트로 텍스트 숫자 여부를 확인한다.
3. 비가시 문자를 치환 후 VALUE로 수치화한다.
4. 업무 규칙을 IF 조건으로 문서화한다.
5. 배열 경계를 항목 단위로 설계한다.

8. 자주 쓰는 조각 코드

/* 0 또는 빈 문자열을 무효로 간주 */ =IF(OR(A2=0, A2=""), "무효", A2)
/* 텍스트 숫자 강제 수치화 */
=--SUBSTITUTE(A2, UNICHAR(160), "")

/* 오류는 제외하되 0은 포함 집계 */
=SUM(IF(ISERROR(A2:A100), 0, A2:A100))

/* 조회 실패만 메시지 */
=IFNA(VLOOKUP(E2, A:B, 2, FALSE), "미존재")

/* 숫자만 필터링 */
=FILTER(A2:A100, ISNUMBER(A2:A100))

/* 분모 검증 후 나눗셈 */
=IF(OR(B2="", --B2=0), "무효", A2/--B2)

9. 동적 배열과 LET/LAMBDA로 유지보수성 향상

LET으로 중간 변수를 선언하면 가독성과 성능이 향상된다. 반복 규칙은 LAMBDA로 재사용한다.

=LAMBDA(x, IF(OR(x="", x=0, NOT(ISNUMBER(x))), "무효", x))(A2) 

검증 로직을 별도 이름 정의로 등록하면 범용 데이터 정제 파이프라인을 구성할 수 있다.

10. 사례 연구: 혼합 데이터 열의 평균 왜곡

상황: 매출 데이터 열에 숫자, 빈 셀, 공백 포함 텍스트, “0” 문자열이 섞여 평균이 왜곡되는 사례이다. IFERROR는 도움이 되지 않는다.

=LET( col, B2:B500, num, --SUBSTITUTE(TRIM(CLEAN(SUBSTITUTE(col, UNICHAR(160), ""))), ",", ""), valid, FILTER(num, ISNUMBER(num)*(num>0)), IF(COUNTA(valid)=0, "유효 데이터 없음", AVERAGE(valid)) ) 

핵심은 오류 처리보다 유효성 조건을 먼저 정의하는 것이다.

11. 피벗·차트 입력값 정화

피벗테이블과 차트 원천 범위에 규칙 위반값이 섞이면 시각화 결과가 왜곡된다. 사전에 필터로 정화한 범위를 명시적으로 참조한다.

=LET(src, Table1[Amount], CLEANED, FILTER(src, ISNUMBER(src)*(src>=0)), CLEANED) 

12. 업무 규칙 문문화 예시

규칙 문장	수식 표현
조회 실패는 “미존재”로 표기한다.	=IFNA(XLOOKUP(...), "미존재")
0과 빈 문자열은 “무효”로 간주한다.	=IF(OR(x=0, x=""), "무효", x)
비가시 문자 제거 후 숫자화한다.	=--TRIM(CLEAN(SUBSTITUTE(x, UNICHAR(160), "")))
배열 연산은 항목 단위로 분기한다.	=IF(조건_배열, 값1, 값2)

FAQ

IFERROR 대신 ISERROR를 써야 하나

IFERROR는 오류일 때 대체값을 바로 반환하는 간결한 구문이다. 특정 오류만 구분하려면 ISNA·IFNA처럼 더 좁은 함수를 사용하거나 ISERROR와 IF를 조합한다.

XLOOKUP의 if_not_found와 IFERROR는 무엇이 다른가

if_not_found는 “검색 실패(#N/A)”에만 적용된다. 반환값이 0·빈문자여도 검색이 성공했다면 if_not_found가 작동하지 않는다. IFERROR는 모든 오류에 반응하지만 0·빈문자 같은 비오류에는 반응하지 않는다.

0을 오류로 간주하려면 어떻게 설계하나

업무 규칙을 IF 조건으로 명시한다. 예: =IF(A2=0, "무효", A2) 또는 LET으로 변수화한 뒤 유효성 검사 후 반환한다.

동적 배열 환경에서 안전한 오류 처리 방법은

전역 IFERROR 대신 요소별 조건 분기를 설계한다. 예: =IF(d=0, "무효", n/d)처럼 분모 검증을 먼저 수행한다.

텍스트 숫자가 섞여 있을 때 한 번에 정규화하는 법은

SUBSTITUTE로 유니코드 공백을 지우고 CLEAN·TRIM으로 정리한 뒤 VALUE 또는 --로 수치화한다.

엑셀에서 중복 데이터 개수 세는 방법 →

엑셀에서 숫자를 텍스트로 변환하는 방법 →

엑셀에서 대소문자 변환(UPPER/LOWER 함수) 사용하는 법 →

추천·관련글

• 엑셀에서 텍스트를 숫자로 변환하는 방법
• 엑셀 공백·특수문자 제거 문제 해결법: CLEAN/TRIM 작동 안 할 때 원인 및 대처 방법
• 엑셀 INDIRECT 참조 오류 해결 팁 – 실무 활용 가이드
• 엑셀 차트 동적 범위 문제 해결과 예방을 위한 완벽 가이드
• 엑셀 데이터 유효성 검사 날짜 범위 문제 해결 가이드
• 엑셀에서 3차원 참조로 여러 시트 데이터 합계하기