이 글의 목적은 엑셀에서 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. 올바른 진단 절차
- 오류인지 비오류인지 판정을 먼저 한다. 셀을 선택하고
F2→Enter로 재계산,수식 탭 > 수식 평가로 단계별 평가를 진행한다. - 자료형 검사를 수행한다.
=ISTEXT(A1),=ISNUMBER(A1),=LEN(A1),=CODE(MID(A1,1,1))로 텍스트·숫자 여부와 비가시 문자를 확인한다. - 업무 규칙을 식으로 명문화한다. “0은 오류로 간주” 같은 규칙을 IF 조건으로 선언한다.
- 배열 경계를 점검한다. 동적 배열 결과에 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. 디버깅 체크리스트
- 수식 평가로 중간값을 확인한다.
- 자료형 테스트로 텍스트 숫자 여부를 확인한다.
- 비가시 문자를 치환 후
VALUE로 수치화한다. - 업무 규칙을 IF 조건으로 문서화한다.
- 배열 경계를 항목 단위로 설계한다.
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 또는 --로 수치화한다.