엑셀 XLOOKUP 오류 해결: 찾기 실패 원인 20가지와 실무 복구 가이드

이 글의 목적은 엑셀 XLOOKUP 함수가 기대한 값을 반환하지 못할 때 발생하는 주요 원인을 체계적으로 진단하고, 현장에서 즉시 적용 가능한 해결 절차와 모범 사례를 제공하는 것이다.

1. XLOOKUP 동작 원리 핵심 정리

XLOOKUP은 =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 형태로 사용하며, lookup_array 에서 lookup_value 와 일치하는 첫 항목을 찾아 return_array 의 같은 위치 값을 반환한다. match_mode 는 일치 방식, search_mode 는 탐색 방향과 알고리즘을 제어한다.

주의 : XLOOKUP은 기본적으로 정확히 일치(0) 모드에서 작동한다. 일치하지 않으면 #N/A가 반환된다.

  
=XLOOKUP(E2, A2:A1000, C2:C1000, "미발견", 0,
1)

위 예시는 E2 값을 A열에서 위에서 아래로(1) 정확히 일치(0) 검색하고, 없으면 "미발견"을 반환한다.

2. 찾기 실패 대표 원인 한눈에 보기

번호	원인	증상	즉시 확인 포인트	권장 해결
1	숫자-텍스트 형식 불일치	#N/A 반환	ISTEXT, ISNUMBER 결과 비교	VALUE, TEXT, '붙은 서식 제거
2	선행/후행 공백 포함	일부만 불일치	LEN 비교 시 길이 차이	TRIM/CLEAN 적용 후 조회
3	숨은 특수문자(비가시)	겉보기에 동일해 보임	CODE/MID로 코드 확인	SUBSTITUTE로 제거
4	유사문자(0/O, 1/l/I)	사번·코드 착시	시각 확인보다 함수 사용	정규화 매핑 테이블 사용
5	match_mode 설정 오류	근사치 반환 또는 #N/A	인수 5번째 확인	정확히 일치(0)로 고정
6	search_mode 방향 오류	중복 중 잘못된 항목	6번째 인수 1 vs -1	필요 시 -1로 마지막값
7	근사치 모드에서 미정렬	엉뚱한 값 반환	오름/내림 정렬 여부	SORT 후 1 또는 -1 사용
8	lookup_array / return_array 길이 불일치	#VALUE!	ROWS/ COLUMNS 비교	동일 범위 크기 보장
9	스필 범위 변동	간헐적 오류	스필 참조(#) 확인	정적 범위로 고정
10	테이블 열 참조 변경	구조화 참조 엇갈림	열 이름 변경 이력	열 이름 고정 및 검증
11	숨겨진 필터/가져오기 전처리	일부 행 누락	필터 아이콘 상태	필터 해제 후 재검색
12	지역 설정 차이(소수점, 쉼표)	문자 처리	DELIMITER 확인	변환 규칙 일관화
13	머지된 셀 포함	비연속 영역 인식 실패	병합 상태 확인	병합 해제 후 정규화
14	공백 행·오염 데이터	엑셀은 보이는데 불일치	COUNTA vs 실제 개수	Power Query로 정제
15	데이터 타입 혼재(날짜/텍스트)	날짜 코드 불일치	ISTEXT/ISNUMBER	DATEVALUE, TEXT 일치화
16	유니코드 정규화(NFC/NFD)	한글/영문 결합문자	문자 길이·CODE 비교	SUBSTITUTE로 재조립
17	범위에 오류값 포함	#N/A 연쇄	AGGREGATE로 검사	IFERROR로 우회
18	불필요 와일드카드	예상치 못한 매칭	* ? 사용 유무	~로 이스케이프
19	외부연결/링크 깨짐	값 갱신 안됨	데이터 원본 상태	값 고정 또는 다시 연결
20	오류 메시지 처리 미흡	보고서 빈칸	if_not_found 미지정	4번째 인수 지정

3. 진단 체크리스트: 60초 내 원인 추려내기

검색값과 기준열을 동시에 선택해 ISTEXT 와 ISNUMBER 로 서로의 타입을 비교한다.
LEN 으로 길이를 비교하여 보이지 않는 공백·문자 존재 여부를 확인한다.
의심 문자를 CODE , UNICODE , MID 로 추출하여 비가시 문자를 식별한다.
중복키가 있는지 COUNTIF(lookup_array, lookup_value) 로 빈도수를 확인한다.
match_mode=0 으로 고정하고 search_mode=1 과 -1 을 비교하여 반환 값이 다른지 본다.

  
=HSTACK( ISTEXT(E2), ISNUMBER(E2), ISTEXT(XLOOKUP(E2, A2:A1000, A2:A1000, "")), LEN(E2), LEN(XLOOKUP(E2, A2:A1000, A2:A1000, "")), COUNTIF(A2:A1000, E2) )

위 한 줄로 타입, 길이, 빈도 정보를 동시에 점검한다.

4. 숫자↔텍스트 불일치 해결

CSV 또는 외부 시스템에서 들어온 코드가 숫자 서식으로 강제 변환되는 경우가 흔하다. 예를 들어 '00123'이 123으로 바뀌면 XLOOKUP 정확 일치가 실패한다.

  
/* 기준열이 텍스트, 검색값이 숫자일 때 */ =XLOOKUP(TEXT(E2,"00000"), A2:A1000, C2:C1000, "없음",
0)
/* 기준열이 숫자, 검색값이 텍스트일 때 */
=XLOOKUP(VALUE(E2), A2:A1000, C2:C1000, "없음",
0)

주의 : 앞자리가 의미 있는 식별자는 항상 텍스트로 관리한다. 테이블 생성 시 열 데이터 형식을 미리 고정한다.

5. 공백·숨은 문자 정리 루틴

다양한 소스에서 합쳐진 데이터는 공백과 비가시 문자를 포함한다. 다음 전처리를 표준으로 삼는다.

  
=LET( raw, A2:A1000, cleaned, TRIM(CLEAN(SUBSTITUTE(raw,CHAR(160)," "))), cleaned )

CHAR(160)은 웹·PDF 기원 데이터에 많은 non-breaking space이다.

6. 와일드카드와 이스케이프

XLOOKUP에서 match_mode=2 를 사용하거나, lookup_value 에 와일드카드를 포함하면 특수 의미를 가진다.

  
/* 와일드카드 문자 자체를 검색할 때 */ =XLOOKUP("ACME~*", A2:A1000, C2:C1000, "없음",
2)

주의 : ~는 이스케이프 문자이다. *, ?를 일반 문자로 취급하려면 앞에 ~를 붙인다.

7. 근사치 모드와 정렬 요건

match_mode=1 (다음 큰 값) 또는 -1 (다음 작은 값)을 사용하면 lookup_array 는 각각 오름차순, 내림차순 정렬이어야 한다. 미정렬 상태에서 근사치 모드는 오동작한다.

  
/* 오름차순 정렬 전용 근사치 */ =XLOOKUP(E2, SORT(A2:A1000,1,1), TAKE(SORT(HSTACK(A2:A1000,C2:C1000),1,1),,2), "없음",
1)

8. 중복키 처리 전략

키가 중복이면 search_mode로 앞/뒤에서 검색 방향을 제어한다. 필요 시 모든 매칭을 반환한다.

  
/* 마지막 항목 반환 */ =XLOOKUP(E2, A2:A1000, C2:C1000, "없음", 0, -1)
/* 모든 매칭 행 반환 */
=FILTER(C2:C1000, A2:A1000=E2, "없음")

9. 날짜·시간 타입 혼재

날짜는 일련번호이며, 텍스트로 가져온 날짜와 일치하지 않는다. 다음 패턴으로 통일한다.

  
/* 텍스트 → 날짜 */ =DATEVALUE(TEXTBEFORE(E2," "))
/* 날짜 → 표준 텍스트 키 */
=TEXT(E2,"yyyy-mm-dd")

10. 유니코드 정규화 문제

한글 자모 결합, 라틴 악센트 등 정규화 차이로 길이가 다르게 나올 수 있다. 가장 안전한 방법은 의심 문자를 제거 후 재생성하는 것이다.

  
/* 눈에 보이지 않는 조합문자 제거 */ =SUBSTITUTE(SUBSTITUTE(A2,CHAR(8237),""),CHAR(8236),"")

11. 구조화 참조와 테이블 안정화

테이블 열 이름 변경으로 참조가 엇갈릴 수 있다. 구조화 참조를 명시적으로 고정한다.

  
/* 테이블 Sales[T_Code]에서 검색, Sales[Result] 반환 */ =XLOOKUP([@Key], Sales[T_Code], Sales[Result], "없음", 0,
1)

주의 : 열 이름 변경을 최소화하고, 열 삽입 시에도 구조화 참조가 유지되는지 즉시 테스트한다.

12. 대량 데이터 성능과 search_mode

행 수가 큰 경우 search_mode=2 (이진 검색, 오름차순) 또는 -2 (이진 검색, 내림차순)를 고려한다. 단, 정렬이 필수이다.

  
=XLOOKUP(E2, A2:A1000000, C2:C1000000, "없음", 0,
2)

13. 오류값 연쇄 차단

조회 실패가 보고서에 빈 칸을 만들지 않게 4번째 인수 또는 IFERROR로 제어한다.

  
=XLOOKUP(E2, A2:A1000, C2:C1000, "미등록",
0) =IFERROR(XLOOKUP(E2, A2:A1000, C2:C1000), "미등록")

14. 파워쿼리·CSV 전처리 파이프라인

반복적으로 유입되는 데이터는 파워쿼리 단계에서 정규화한다.

  
// 파워쿼리 단계 개요
1) 원본 불러오기
2) 열 형식 텍스트로 강제, 앞/뒤 공백 제거
3) non-breaking space 치환
4) 키 열 대문자화 또는 포맷 통일
5) 중복 제거 또는 최신 행만 유지

15. 진단 매크로 없이 수식만으로 만드는 "클린 키"

조회 키를 표준화한 보조열을 만들면 오류율이 급격히 낮아진다.

  
=LET( t, A2, u1, TRIM(CLEAN(SUBSTITUTE(t,CHAR(160)," "))), u2, UPPER(u1), u3, SUBSTITUTE(SUBSTITUTE(u2,"-","")," ",""), IFERROR(VALUE(u3), u3) )

이 "클린 키"를 lookup_array로 사용하면 숫자/텍스트 혼재와 공백 문제를 동시에 제거한다.

16. 실무 패턴별 처방전

16.1 사번·제품코드에 앞자리 0 유지

  
=XLOOKUP(TEXT(E2,"000000"), A2:A1000, C2:C1000, "없음",
0)

16.2 대소문자 구분이 필요한 경우

XLOOKUP은 기본적으로 대소문자를 구분하지 않는다. XMATCH와 INDEX를 결합한다.

  
=LET( pos, XMATCH(TRUE, EXACT(E2, A2:A1000),
0), IFERROR(INDEX(C2:C1000, pos), "없음") )

16.3 복합 키 조회

  
=XLOOKUP( TEXTJOIN("|",,TEXT(E2,"yyyymmdd"), UPPER(F2), G2), HSTACK(TEXT(A2:A1000,"yyyymmdd"), UPPER(B2:B1000), C2:C1000)&"|", D2:D1000, "없음", 0 )

16.4 마지막 거래일 값 가져오기

  
=XLOOKUP(E2, A2:A1000, C2:C1000, "없음", 0, -1)

17. 재현 테스트 시트 구성 템플릿

  
열 A: 원시키(raw_key) 열 B: 클린키(clean_key) 열 C: 반환값(return) 열 E: 검색값(lookup_value)
B2 수식:
=LET(t,A2, TRIM(CLEAN(SUBSTITUTE(t,CHAR(160)," "))))

F2 결과:
=XLOOKUP(TRIM(E2), B:B, C:C, "미등록", 0,
1)

18. 보고서 안전장치: 결과 품질지표

조회 성공률, 중복키율, 미정렬율을 숫자로 관리한다.

  
/* 조회 성공률 */ =1 - (COUNTIF(F2:F1000,"미등록")/COUNTA(E2:E1000))
/* 중복키율 */
=1 - (COUNTA(UNIQUE(A2:A1000))/COUNTA(A2:A1000))

/* 정렬 검사(오름차순 위반 카운트) */
=SUM(--(A2:A999 > A3:A1000))

19. 빈번한 질문과 즉답

같은 값이 여러 번 나오면 어떤 것을 반환하나? 기본은 첫 번째, search_mode=-1은 마지막을 반환한다.
근사치 모드는 왜 위험한가? 기준열 정렬을 보장하지 않으면 잘못된 값을 반환한다.
테이블 열이 바뀌면 수식이 망가지나? 구조화 참조를 사용하면 대부분 안전하다.

20. 최종 점검 루틴: 붙여넣기만 하면 되는 7단계

기준열·검색값에 TRIM, CLEAN, CHAR(160) 치환을 적용한다.
숫자/텍스트 타입을 TEXT 또는 VALUE로 일치화한다.
대상 열이 정렬 요구가 있으면 SORT로 정렬한다.
중복키 의도 확인 후 search_mode를 1 또는 -1로 선택한다.
if_not_found 메시지를 지정한다.
필요 시 보조 "클린 키" 열을 생성해 XLOOKUP 기준으로 사용한다.
결과 품질지표를 계산해 조회 신뢰도를 수치로 기록한다.

FAQ

XLOOKUP이 #N/A를 반환할 때 가장 먼저 무엇을 보나?

검색값과 기준열의 데이터 형식이 같은지 ISTEXT, ISNUMBER, LEN으로 즉시 확인한다. 불일치가 있으면 VALUE 또는 TEXT로 통일한다.

근사치 모드에서 정렬은 꼭 필요한가?

필수이다. 오름차순은 match_mode=1, 내림차순은 match_mode=-1에서 각각 요구된다. 미정렬이면 오동작한다.

중복키에서 마지막 값을 반환하려면?

search_mode=-1을 사용한다. 예: =XLOOKUP(E2, A:A, C:C, "없음", 0, -1) 형태로 설정한다.

대소문자를 구분하려면?

XLOOKUP 단독으로는 불가하다. EXACT와 XMATCH, INDEX를 결합한다.

숨은 공백을 한 번에 제거하려면?

TRIM(CLEAN(SUBSTITUTE(셀,CHAR(160)," ")))를 표준 전처리로 사용한다.

ZeroError_holic

이 블로그 검색

파워포인트 자막(라이브 캡션) 안될 때 1분 진단과 완전 해결 가이드