엑셀에서 =XLOOKUP(...) 수식을 입력했는데 찾으려는 값이 원본 표에 분명히 있는데도 #N/A 오류가 발생하는 경우가 있습니다. 상품코드, 직원번호, 주문번호처럼 화면에는 똑같아 보이는 데이터가 실제로는 다른 형태로 저장되어 있을 수 있기 때문입니다.
특히 CSV 파일을 가져온 뒤 숫자가 문자로 저장되거나, 복사·붙여넣기한 데이터에 보이지 않는 공백이 들어가면 오류 원인을 찾기가 어려워집니다. 이때 수식만 계속 바꾸면 문제를 더 복잡하게 만들 수 있습니다.
이 글에서는 XLOOKUP #N/A 오류 해결 방법을 원본 데이터 확인부터 숫자·문자 형식, 공백, 숨은 문자, 검색 범위까지 차례로 설명합니다. 예제 XLSX 파일로 오류를 재현하고 수정할 수 있도록 구성했으며, Microsoft 365·Excel 2021·2024를 중심으로 안내합니다.
핵심 요약
① 실제 값 존재 여부 확인 → ② 숫자·문자 형식 일치 → ③ 앞뒤 공백·숨은 문자 제거 → ④ 조회 범위·일치 방식 확인 → ⑤ 수정 후 결과 검증 순서로 점검하세요. if_not_found를 넣으면 #N/A 대신 안내 문구를 표시할 수 있지만 오류의 근본 원인이 해결되는 것은 아닙니다.
1. XLOOKUP에서 #N/A가 나오는 정확한 의미
XLOOKUP은 지정한 검색 범위에서 조회 값을 찾아 같은 행(또는 열)에 있는 결과를 반환하는 함수입니다. 기본값은 정확히 일치하는 값을 찾는 방식이며, 일치하는 항목을 찾지 못하고 네 번째 인수인 if_not_found를 생략하면 #N/A가 나타납니다.
=XLOOKUP(조회값, 조회범위, 반환범위, [못 찾았을 때 표시], [일치방식], [검색방향])예를 들어 원본 데이터의 A2:A9에 상품코드가 있고 B2:B9에 상품명이 있다면 다음처럼 사용할 수 있습니다.
=XLOOKUP(E2,$A$2:$A$9,$B$2:$B$9)문제는 조회 범위에 값이 보인다고 해서 Excel이 반드시 같은 값으로 인식하는 것은 아니라는 점입니다. 실제로 저장된 숫자/문자 유형, 공백, 코드 문자가 다르면 정확히 일치하지 않을 수 있습니다.
| 표시된 현상 | 우선 확인할 사항 | 오류 외 다른 가능성 |
|---|---|---|
| 원본에는 1001이 있는데 검색 실패 | 숫자 1001인지 문자 "1001"인지 | 숫자·문자 혼용 |
| 상품명이 같아 보이는데 검색 실패 | 앞뒤 공백과 숨은 문자 | 외부 시스템 복사 데이터 |
| 새로 추가한 코드만 검색 실패 | 조회 범위 마지막 행 | 범위에서 신규 행 누락 |
| 문자 패턴으로 찾으려는데 실패 | 정확일치/와일드카드 설정 | match_mode 미지정 |
2. XLOOKUP #N/A 오류의 주요 원인 7가지
| 원인 | 실제 상황 | 빠른 진단 |
|---|---|---|
| ① 원본에 값 자체가 없음 | 입력한 코드가 원본에 없는 경우 | 오타·누락·공백 포함 여부부터 확인 |
| ② 숫자와 문자가 서로 다름 | 숫자 1001 ↔ 텍스트 "1001" | ISNUMBER·ISTEXT로 비교 |
| ③ 앞뒤 일반 공백 | "AB-20" ↔ "AB-20 " | LEN·TRIM 확인 |
| ④ NBSP 같은 특수 공백 | "C 040"의 공간이 CHAR(160)인 경우 | SUBSTITUTE·CHAR(160) 사용 |
| ⑤ 줄바꿈·인쇄 불가 문자 | 복사한 항목에 줄바꿈이나 제어 문자 포함 | CLEAN 등으로 정리 |
| ⑥ 조회 범위가 잘림 | 원본은 9행까지인데 수식은 5행까지만 조회 | lookup_array 끝 행 확인 |
| ⑦ 일치 방식이 의도와 다름 | "P-*"처럼 패턴을 기대했으나 기본 정확일치 사용 | match_mode 0/2 확인 |
이외에 찾는 열을 잘못 지정하거나 코드의 앞자리 0이 사라지는 문제도 있습니다. 단, 조회 배열과 반환 배열의 크기가 맞지 않는 문제는 #VALUE! 등 다른 오류로 나타날 수 있어 #N/A의 원인과 구분하는 것이 좋습니다.
3. 숫자 1001과 문자 "1001"이 달라서 못 찾을 때
가장 흔한 실무 사례 중 하나는 조회값이 문자형 1001인데 원본 코드는 숫자형 1001로 저장된 경우입니다. 셀에 표시되는 모양만 보고 판단하면 이 차이를 놓치기 쉽습니다.
STEP 1. 원본과 조회값의 실제 데이터 유형 확인
숫자 1001이 입력된 A2 셀과 조회값 E2 셀의 유형을 각각 확인해 봅니다.
=ISNUMBER(A2)
=ISTEXT(E2)첫 번째가 TRUE, 두 번째도 TRUE라면 원본은 숫자이고 조회값은 문자입니다. 셀에 텍스트 서식을 적용하는 것만으로 이미 저장된 값의 데이터 유형이 항상 바뀌지는 않습니다.
STEP 2. 원본이 숫자 코드라면 조회값을 숫자로 변환
=XLOOKUP(VALUE(E2),$A$2:$A$9,$B$2:$B$9,"미등록",0)VALUE(E2)는 숫자로 표현할 수 있는 텍스트를 숫자로 변환합니다. 단, 원본 상품코드가 00123처럼 앞자리 0을 의미 있게 사용하는 경우에는 VALUE를 사용하지 마세요. 그런 식별자는 전체 데이터를 문자형으로 통일하는 편이 안전합니다.
4. 값 앞뒤의 공백 때문에 #N/A가 뜰 때
원본이 AB-20 처럼 끝에 공백을 포함하고 있다면, 조회값 AB-20과 다른 문자열로 비교될 수 있습니다. 일반 공백은 TRIM으로 정리할 수 있습니다.
=LEN(A5)
=TRIM(A5)공백을 정리한 값을 새 열에 만들고, XLOOKUP의 조회 범위를 정리된 열로 변경하면 오류를 줄일 수 있습니다. 원본 코드 열을 즉시 덮어쓰지 말고 복사본에서 확인하세요.
=XLOOKUP(G2,$E$2:$E$9,$B$2:$B$9,"미등록",0)위 수식은 원본데이터 E열에 정리된 상품코드가 있고 G2에 조회할 코드가 있는 상황을 가정합니다. 실제 실습 파일의 해결 수식도 원본데이터 E열을 참조합니다.
5. TRIM으로도 해결되지 않는 숨은 공백·문자 처리
웹페이지에서 복사한 데이터에는 일반 공백과 모양이 비슷한 줄 바꿈하지 않는 공백(NBSP)이 섞일 수 있습니다. Microsoft 공식 문서에 따르면 TRIM은 ASCII 공백(32)을 대상으로 하므로 CHAR(160)을 단독으로 제거하지 못합니다.
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))위 수식은 NBSP를 일반 공백으로 바꾼 후 공백을 정리합니다. 줄 바꿈이나 탭처럼 제어 문자가 문제인 경우에는 CLEAN을 추가할 수 있습니다.
=TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160)," "))다만 모든 유니코드 제어 문자나 특수 공백이 이 조합으로 없어지는 것은 아닙니다. 필요한 경우 LEN, UNICODE 등으로 해당 문자의 실제 값을 확인한 뒤 안전하게 정리해야 합니다.
6. 조회 범위와 match_mode 설정 확인하기
① 검색 범위가 실제 데이터의 마지막 행을 포함하는가?
데이터가 A9까지 있는데 수식이 $A$2:$A$5만 조회하면 A8의 코드가 있어도 발견하지 못합니다. 조회 범위를 새 데이터까지 확장하고, 반환 범위도 같은 크기인지 확인하세요.
=XLOOKUP(E2,$A$2:$A$9,$B$2:$B$9,"미등록",0)② 별표(*)를 와일드카드로 사용하려면 일치 방식 변경
XLOOKUP은 match_mode를 생략하면 정확히 일치하는 값만 찾습니다. 예를 들어 P-*를 패턴으로 검색하려면 여섯 번째가 아닌 다섯 번째 인수를 2로 지정해야 합니다.
=XLOOKUP("P-*",$A$2:$A$9,$B$2:$B$9,"미등록",2)여기서 2는 와일드카드 검색을 뜻하며, *는 여러 문자를 나타냅니다. 일반적인 상품번호처럼 정확히 같은 코드를 찾는 경우에는 0을 사용하는 편이 결과 검증에 유리합니다.
③ 중복 코드가 있어도 #N/A가 되는 것은 아닙니다
같은 코드가 여러 번 있으면 기본 XLOOKUP은 처음 발견한 일치 항목을 반환합니다. 중복 코드는 잘못된 상품명이 나오는 원인이 될 수 있지만, 일반적으로 그것만으로 #N/A가 발생하는 것은 아닙니다.
7. if_not_found는 오류를 숨기는 설정이지 원인 해결은 아닙니다
XLOOKUP의 네 번째 인수인 if_not_found에 문구를 지정하면 검색 결과가 없을 때 #N/A 대신 사람이 이해하기 쉬운 메시지를 보여줄 수 있습니다.
=XLOOKUP(E2,$A$2:$A$9,$B$2:$B$9,"미등록 코드",0)하지만 조회값이 틀렸거나 데이터 유형이 달라서 매칭이 실패한 상태는 그대로입니다. 특히 주문번호·금액·재고 관리에서는 "미등록 코드"가 표시되는 이유를 먼저 확인해야 데이터 누락을 막을 수 있습니다.
주의: 다른 오류와 혼동하지 마세요
#NAME?는 함수 지원 버전이나 이름 인식 문제와 관련될 수 있고, #VALUE!는 배열 크기나 인수 구성 문제 등에서 발생할 수 있습니다. 특히 XLOOKUP은 Excel 2016·2019에서 지원되지 않으며 Microsoft 365·Excel 2021·2024 등 지원 버전을 확인하세요.
8. 엑셀 실습 파일로 실제 오류 재현하고 해결하기
아래 예제는 가상의 상품코드·상품명 데이터로 구성되어 있습니다. 서로 다른 숫자·문자 유형, 앞뒤 공백, NBSP 공백, 누락된 범위 등 여러 조건을 비교할 수 있습니다. 회사 자료나 개인정보 없이 안전하게 연습하도록 만들었습니다.
| 시트 이름 | 내용 |
|---|---|
| 실습안내 | 학습 순서와 버전·주의사항 |
| 원본데이터 | 숫자형 코드, 문자형 코드, 일반 공백, NBSP가 포함된 데이터 |
| 오류재현 | 일부러 일치하지 않게 설정한 XLOOKUP 사례 |
| 해결실습 | 형식 정리·범위 수정·일치 방식 변경 수식 비교 |
| 공식참고 | Microsoft 공식 문서 URL |
📎 XLOOKUP 실습 파일 다운로드
첨부된 43_XLOOKUP_NA_오류재현_해결실습.xlsx를 내려받아 실습안내 → 원본데이터 → 오류재현 → 해결실습 순서로 확인해 보세요. 예제는 실습을 위한 가상 상품 데이터입니다.
먼저 오류재현 시트에서 검색값과 수식을 살펴본 뒤 해결실습 시트의 수정 수식을 확인합니다. 사용자의 Excel 버전에서 실제 계산된 결과를 기대값과 비교하세요. 학습 파일은 공식을 담고 있으나 특정 PC의 Excel 앱에서 실행 검증한 스크린샷은 아닙니다.
원본데이터시트에서 상품코드 1001, 00123, AB-20, C 040의 실제 형식을 확인합니다.오류재현시트에서 조회값과 원본의 차이를 비교합니다.해결실습시트의 해결 수식을 확인하고, 조회값을 바꿔 결과 변화를 관찰합니다.- 입력값 정리 후 기대 결과가 나오면 회사의 실제 파일에 적용하기 전 백업 복사본에서 다시 검증합니다.
9. XLOOKUP #N/A 오류 최종 해결 순서
- 찾는 상품코드·사번·주문번호가 실제 원본에 있는지 확인합니다.
- 원본과 조회값이 모두 숫자인지 또는 모두 문자인지
ISNUMBER·ISTEXT로 비교합니다. - 앞뒤 공백을
LEN·TRIM으로 점검합니다. - 웹페이지에서 복사한 데이터라면
CHAR(160)같은 특수 공백을 확인합니다. lookup_array의 첫 행·마지막 행과 조회 열을 확인합니다.- 조회 목적에 맞게
match_mode=0또는 2 등을 선택합니다. - 수정 후 실제 데이터 몇 건을 수동 검산하고, 그다음에
if_not_found로 사용자용 문구를 설정합니다.
한 줄 정리
XLOOKUP의 #N/A는 함수 고장보다 “검색한 값과 실제 저장된 값이 일치하지 않음”을 알리는 경우가 많습니다. 수식부터 바꾸기보다 데이터 유형과 숨은 문자, 조회 범위를 먼저 확인하세요.
자주 묻는 질문(FAQ)
Q1. XLOOKUP은 값이 있어도 왜 #N/A가 나오나요?
눈에 보이는 글자가 같아도 숫자·문자 형식, 숨은 공백, 검색 범위 차이 때문에 정확히 일치하지 않을 수 있습니다. 먼저 원본 값과 참조 범위를 확인하세요.
Q2. 숫자로 표시된 코드를 전부 VALUE로 바꾸면 되나요?
아니요. 00123처럼 앞자리 0이 중요한 식별코드는 숫자로 바꾸면 의미가 달라질 수 있습니다. 코드의 의미를 확인하고 통일해야 합니다.
Q3. TRIM을 써도 공백이 없어지지 않습니다.
웹페이지나 시스템에서 가져온 NBSP(CHAR(160))가 원인일 수 있습니다. SUBSTITUTE와 TRIM을 함께 사용해 비교하되 원본은 백업하세요.
Q4. XLOOKUP에서 중복된 값이 있으면 #N/A가 발생하나요?
중복 자체가 #N/A의 직접 원인은 아닙니다. 기본값에서는 첫 번째로 찾은 일치 항목을 반환하므로 중복 키가 정확한 결과를 주는지 별도로 확인해야 합니다.
Q5. Excel 2019에서 XLOOKUP이 작동하지 않는다면?
Microsoft 안내에 따르면 Excel 2016·2019는 XLOOKUP을 지원하지 않습니다. 지원 버전을 사용하거나 VLOOKUP·INDEX/MATCH와 같은 대체 수식을 검토하세요.
관련 글
- 엑셀 CSV 파일 한글 깨짐 해결 방법｜UTF-8·CP949 인코딩 확인 — CSV를 잘못 불러와 데이터가 예상과 다르게 저장되는 문제
- 엑셀 숫자가 날짜로 자동 변경될 때 해결 방법 — 상품코드·숫자형 문자열을 입력할 때 원본을 보존하는 방법