📑 목차
VLOOKUP이나 XLOOKUP에서 분명히 존재하는 사번·상품코드를 찾았는데 #N/A가 나오는 경우가 있습니다. 화면에는 똑같아 보여도 한쪽은 숫자, 다른 쪽은 문자이거나 값 앞뒤에 보이지 않는 공백이 남아 있으면 정확히 일치하지 않습니다.
오류를 IFERROR로 가리기 전에 조회값 존재 여부 → 숫자·문자 형식 → 숨은 공백 → 조회 범위 순서로 확인해야 원인을 제대로 고칠 수 있습니다. 바로 적용할 수식과 점검 순서를 정리했습니다.
VLOOKUP·XLOOKUP 오류 원인 빠른 진단표
| 증상 | 주요 원인 | 먼저 확인할 것 |
|---|---|---|
| #N/A | 값 없음·형식 불일치·공백 | COUNTIF로 존재 여부 확인 |
| 엉뚱한 값 반환 | VLOOKUP 근사값 사용 | 마지막 인수를 FALSE로 |
| #REF! | 반환 열 번호가 범위 밖 | 열 번호·삭제된 열 확인 |
| #VALUE! | 인수·범위 크기 문제 | 조회·반환 배열 길이 확인 |
| #NAME? | 함수 미지원·이름 오타 | Excel 버전 확인 |
1. 조회값이 원본 표에 실제로 있는지 확인
먼저 조회값 B2가 원본 코드 범위 A2:A100에 존재하는지 확인합니다.
- 결과가 1 이상: 값은 있으므로 형식·공백·수식을 점검합니다.
- 결과가 0: 원본에 값이 없거나 두 값의 형식이 다를 수 있습니다.
- 2 이상: 중복 코드가 있어 첫 번째 값만 반환될 수 있습니다.
철자, 하이픈, 대소문자보다 먼저 앞뒤 공백과 숫자·문자 여부를 확인하는 것이 빠릅니다.
2. 숫자와 문자 형식 불일치 고치기
상품코드 1001이 한쪽에서는 숫자, 다른 쪽에서는 텍스트 ‘1001’로 저장되면 정확히 일치하지 않아 #N/A가 날 수 있습니다. 셀 왼쪽 위의 녹색 삼각형이나 정렬 방향만으로 단정하지 말고 아래 함수로 확인하세요.
=ISNUMBER(A2)
문자형 숫자를 숫자로 변환
- 오류 표시 메뉴에서 숫자로 변환 선택
- 보조열 수식: =VALUE(A2)
- 간단한 변환: =--A2
숫자를 문자 코드로 맞추기
앞자리 0이 중요한 상품코드·사번은 숫자로 바꾸면 안 됩니다. 정해진 자릿수에 맞춰 TEXT 함수를 사용합니다.
3. 앞뒤 공백과 숨은 문자 제거
다른 시스템이나 웹페이지에서 붙여넣은 데이터에는 눈에 보이지 않는 공백과 인쇄 불가능 문자가 섞일 수 있습니다.
=CLEAN(A2)
=TRIM(CLEAN(A2))
TRIM은 일반적인 앞뒤 공백과 중복 공백을 정리하고 CLEAN은 일부 인쇄되지 않는 문자를 제거합니다. 웹에서 복사한 줄 바꿈 없는 공백은 TRIM만으로 남을 수 있으므로 아래처럼 SUBSTITUTE를 함께 사용할 수 있습니다.
정리한 보조열을 복사해 ‘값 붙여넣기’한 뒤 조회 범위로 사용하면 원본 전체의 오류를 줄일 수 있습니다.
4. VLOOKUP 수식 제대로 작성하기
상품코드 B2를 원본표 A2:C100의 첫 열에서 찾아 세 번째 열의 금액을 가져오는 정확 일치 수식입니다.
- B2: 찾을 상품코드
- $A$2:$C$100: 조회표, 찾을 값은 반드시 첫 번째 열에 있어야 함
- 3: 조회표 안에서 결과를 가져올 세 번째 열
- FALSE: 정확히 같은 값만 찾기
FALSE를 생략하거나 TRUE를 사용하면 근사값 검색이 적용될 수 있습니다. 표가 알맞게 정렬되지 않았다면 존재하지 않는 값에도 엉뚱한 결과가 나올 수 있으므로 코드 조회에는 FALSE가 안전합니다.
5. XLOOKUP으로 더 쉽게 조회하기
XLOOKUP은 조회 열과 반환 열을 따로 지정하므로 반환값이 조회 열의 왼쪽에 있어도 사용할 수 있습니다.
네 번째 인수에 ‘코드 없음’을 넣으면 일치값이 없을 때 #N/A 대신 안내문을 표시합니다. 다만 숫자·문자 불일치나 공백 문제가 해결되는 것은 아니므로 원본 데이터 정리가 먼저입니다.
버전 주의: Microsoft 공식 안내상 XLOOKUP은 Excel 2016과 Excel 2019에서 사용할 수 없습니다. 구버전과 공유한다면 VLOOKUP 또는 INDEX·MATCH를 검토하세요.
6. 오류를 감추기 전에 원인을 먼저 수정
IFNA와 IFERROR를 사용하면 사용자 화면을 깔끔하게 만들 수 있습니다.
=IFERROR(XLOOKUP(B2,$A$2:$A$100,$C$2:$C$100),"확인 필요")
IFNA는 #N/A만 처리하므로 조회 실패를 구분하기 좋습니다. IFERROR는 #REF!, #VALUE! 등 다른 오류까지 모두 가릴 수 있어 잘못된 범위나 열 번호를 놓칠 수 있습니다. 먼저 원인을 확인한 뒤 최종 보고서에 적용하세요.
VLOOKUP과 XLOOKUP 차이
| 구분 | VLOOKUP | XLOOKUP |
|---|---|---|
| 검색 방향 | 첫 열에서 오른쪽 반환 | 왼쪽·오른쪽 모두 가능 |
| 정확 일치 | FALSE 지정 | 기본값 |
| 열 삽입 영향 | 열 번호 수정 가능성 | 반환 범위를 직접 지정 |
| 못 찾을 때 문구 | IFNA 등 결합 | 네 번째 인수에 바로 지정 |
| 구버전 호환 | 폭넓게 지원 | Excel 2016·2019 미지원 |
#N/A 해결 체크리스트
- COUNTIF로 조회값이 원본에 있는지 확인합니다.
- 조회값과 원본 코드가 모두 숫자 또는 모두 문자인지 확인합니다.
- TRIM·CLEAN으로 숨은 공백과 문자를 정리합니다.
- VLOOKUP의 마지막 인수가 FALSE인지 확인합니다.
- VLOOKUP 조회값이 표의 첫 번째 열에 있는지 확인합니다.
- XLOOKUP의 조회 배열과 반환 배열 행 수가 같은지 확인합니다.
- 원인을 해결한 뒤 IFNA로 안내문을 표시합니다.
자주 묻는 질문
Q1. 값이 분명히 있는데 VLOOKUP이 #N/A를 반환하는 이유는 무엇인가요?
숫자와 문자 형식이 다르거나 앞뒤에 숨은 공백이 있을 가능성이 큽니다. ISTEXT·ISNUMBER와 TRIM으로 확인하세요.
Q2. VLOOKUP 마지막 인수는 FALSE와 TRUE 중 무엇을 써야 하나요?
사번·상품코드처럼 정확히 같은 값을 찾을 때는 FALSE를 사용합니다. TRUE는 근사값 검색으로 정렬 조건이 필요합니다.
Q3. #N/A를 빈칸으로 표시하려면 어떻게 하나요?
수식을 IFNA로 감싸고 두 번째 인수에 빈 문자열을 넣습니다. 예: =IFNA(VLOOKUP(...),"")입니다.
Q4. XLOOKUP에서 #NAME?이 표시되는 이유는 무엇인가요?
함수 이름 오타 또는 지원하지 않는 Excel 버전일 수 있습니다. Excel 2016과 2019에서는 XLOOKUP을 지원하지 않습니다.
Q5. TRIM을 사용해도 공백이 없어지지 않아요.
웹에서 복사한 줄 바꿈 없는 공백일 수 있습니다. SUBSTITUTE로 CHAR(160)을 일반 공백으로 바꾼 뒤 TRIM을 적용하세요.