엑셀 VLOOKUP #N/A 오류가 발생하는 원인과 해결 방법
엑셀에서 VLOOKUP 함수를 입력했는데 #N/A 오류가 나타나면 먼저 조회할 값이 원본 표에 있는지 확인해야 합니다.
값이 실제로 존재하더라도 숫자와 텍스트 형식이 다르거나, 눈에 보이지 않는 공백이 끼어 있으면 엑셀이 서로 다른 값으로 인식할 수 있습니다.
특히 직원번호, 거래처코드, 상품코드를 다른 파일에서 복사해 연결할 때 이런 문제가 자주 발생합니다. 수식을 무작정 다시 입력하기보다는 조회값과 데이터 구조를 순서대로 살펴보는 편이 빠릅니다.
이 글에서는 엑셀 VLOOKUP #N/A 오류의 대표 원인 5가지를 작은 상품 목록 예제로 확인하고, 바로 적용할 수 있는 해결 수식까지 정리합니다.
엑셀 VLOOKUP #N/A 오류는 무슨 뜻일까?
VLOOKUP은 지정한 표의 첫 번째 열에서 값을 찾은 뒤, 같은 행의 다른 열에 있는 정보를 가져오는 함수입니다. #N/A는 보통 요청한 조회값과 일치하는 항목을 찾지 못했다는 뜻입니다. 다만 원본에 데이터가 아예 없다는 의미로만 받아들이면 문제를 놓칠 수 있습니다.
대표적인 원인을 먼저 비교하면 다음과 같습니다.
| 점검 항목 | 문제 상황 | 우선 조치 |
|---|---|---|
| 조회값 누락 | 상품코드가 원본에 없음 | 코드·철자 확인 |
| 조회 열 위치 | 찾는 코드가 범위 첫 열이 아님 | 범위 다시 지정 |
| 데이터 형식 | 숫자 1001과 문자 "1001" 혼용 | 양쪽 형식 통일 |
| 숨은 문자 | 앞뒤 공백·붙여넣기 문자 포함 | 공백 정리 |
| 일치 방식 | TRUE 또는 인수 생략 | FALSE로 재확인 |
VLOOKUP 수식으로 오류를 직접 확인하는 예제
상품 목록을 A1:C5에 입력한다고 가정하겠습니다. A열은 상품코드, B열은 상품명, C열은 단가입니다.
| 행 | A열 상품코드 | B열 상품명 | C열 단가 |
|---|---|---|---|
| 2 | P-101 | 텀블러 | 12,900 |
| 3 | P-102 | 노트 | 4,500 |
| 4 | P-103 | 볼펜 | 1,800 |
| 5 | P-104 | 파일 | 3,200 |
E2 셀에 P-102를 적고 F2 셀에 아래 수식을 입력합니다.
=VLOOKUP(E2,$A$2:$C$5,2,FALSE)
이 수식은 A열에서 P-102를 찾고, 같은 행의 두 번째 열인 B열에서 노트를 가져옵니다. E2를 P-999로 변경하면 원본에 해당 코드가 없기 때문에 #N/A가 표시됩니다.
여기서 숫자 2는 결과를 가져올 열 번호이며, FALSE는 정확히 같은 값만 찾겠다는 의미입니다. 달러 기호($)는 수식을 아래로 복사해도 조회 범위가 바뀌지 않도록 고정합니다.
엑셀 VLOOKUP #N/A 오류 원인 5가지와 해결 방법
1. 찾는 값이 원본 표에 없는 경우
가장 먼저 E2의 코드와 A열의 코드를 비교합니다. 영문 O와 숫자 0, 하이픈의 유무처럼 육안으로 놓치기 쉬운 차이도 확인하세요. 신규 상품이 아직 원본 목록에 반영되지 않았거나, 조회 범위가 최근 추가된 행을 포함하지 않을 수도 있습니다.
조회 범위를 확인할 때는 수식의 두 번째 인수인 $A$2:$C$5를 살펴보면 됩니다. 신규 데이터가 6행에 추가됐다면 기존 범위에서 검색되지 않으므로 범위를 늘리거나 엑셀 표 기능을 활용해야 합니다.
2. 조회하려는 열이 범위의 첫 번째 열이 아닌 경우
VLOOKUP은 지정 범위의 맨 왼쪽 열에서 조회값을 찾습니다. 예를 들어 상품명을 기준으로 상품코드를 가져오려는데 범위를 A:C로 지정하면, 함수는 상품명이 아니라 A열의 상품코드에서 검색을 시작합니다.
조회값이 포함된 열부터 범위를 지정하고, 결과 열이 그 오른쪽에 있는지 확인해야 합니다. 오른쪽의 상품명으로 왼쪽 상품코드를 가져오는 작업이라면 사용 가능한 엑셀 버전에서 XLOOKUP을 고려할 수 있습니다.
3. 숫자와 텍스트 형식이 서로 다른 경우
겉으로 모두 1001이라고 보여도 한쪽은 숫자, 다른 쪽은 문자일 수 있습니다. 조회값 셀에 =ISNUMBER(E2)를 입력하고 원본 코드 셀에는 =ISTEXT(A2)를 사용하면 형식을 구분하는 데 도움이 됩니다.
셀 표시 형식만 '숫자'로 바꾼다고 기존 텍스트 값이 모두 숫자로 변환되는 것은 아닙니다. 필요한 경우 데이터 → 텍스트 나누기 → 마침 등을 이용해 실제 값의 형식을 통일하세요. 앞에 0이 붙는 사번이나 우편번호는 숫자로 바꾸면 0이 사라질 수 있으므로 텍스트로 유지하는 것이 안전합니다.
4. 앞뒤 공백이나 보이지 않는 문자가 포함된 경우
외부 프로그램에서 코드를 복사하면 일반 공백이나 줄바꿈, 특수 공백이 섞이기도 합니다. 예를 들어 P-102 뒤에 공백이 하나 붙어 있으면 정확한 일치 검색에서 다른 값으로 취급될 수 있습니다.
조회값에 일반 공백과 일부 특수 공백이 섞여 있다면 아래와 같이 정리할 수 있습니다.
=TRIM(SUBSTITUTE(E2,CHAR(160)," "))
TRIM은 일반적인 불필요한 공백을 정리하고, SUBSTITUTE는 웹에서 복사할 때 포함되기 쉬운 CHAR(160) 공백을 바꿉니다. 단, 원본 표의 코드에도 공백이 있다면 원본 열 역시 정리해야 합니다. 정리 전에는 별도 열에서 결과를 확인하고 원본을 보관하세요.
5. 마지막 인수가 TRUE이거나 생략된 경우
VLOOKUP의 마지막 인수를 생략하면 기본적으로 TRUE, 즉 근삿값 검색이 적용됩니다. 정확한 상품코드나 직원번호를 찾아야 할 때 TRUE를 쓰면 #N/A가 발생하거나 엉뚱한 행의 값이 반환될 수 있습니다.
코드와 ID를 검색할 때는 =VLOOKUP(E2,$A$2:$C$5,2,FALSE)처럼 FALSE를 명시하세요. TRUE가 필요한 구간별 요율표 같은 경우에는 첫 열의 정렬 상태도 확인해야 합니다.
오류를 빠르게 찾는 실무 점검 순서
파일에 수식이 수백 개 있더라도 오류가 난 셀 하나를 먼저 골라 점검하는 것이 효율적입니다. 다음 순서로 확인하면 오류의 원인을 좁힐 수 있습니다.
- 조회값 확인: 코드가 원본에 있는지, 오타는 없는지 비교합니다.
- 범위 확인: 원본의 마지막 행까지 포함하고 조회 열이 가장 왼쪽에 있는지 봅니다.
- 형식 확인: 숫자와 텍스트가 섞였는지 확인합니다.
- 공백 확인: 복사한 데이터에 불필요한 공백이 있는지 검사합니다.
- 수식 확인: 마지막 인수 FALSE와 범위 고정($)을 확인합니다.
수식을 여러 행에 복사했을 때 아래쪽에서만 문제가 생겼다면 상대참조 때문에 조회 범위가 내려갔을 가능성도 있습니다. 첫 번째 정상 셀과 오류 셀의 수식을 나란히 비교해 보세요.
IFNA로 #N/A 표시를 바꿀 때 주의할 점
거래처에서 받은 목록에 일부 코드가 없더라도 보고서를 제출해야 하는 상황이 있습니다. 이때 #N/A 대신 '미등록'을 표시하려면 IFNA를 이용할 수 있습니다.
=IFNA(VLOOKUP(E2,$A$2:$C$5,2,FALSE),"미등록")
다만 오류 표시를 바꾸는 것과 오류 원인을 해결하는 것은 다릅니다. 실제로 있어야 할 데이터가 빠진 상태라면 '미등록'으로 바꾸는 순간 누락 사실을 놓칠 수 있습니다. 먼저 데이터의 정확성을 확인한 뒤 표시 방식을 결정하세요. IFERROR는 다른 종류의 오류까지 처리하므로 VLOOKUP의 #N/A만 구별하려면 IFNA가 더 명확합니다.
자주 묻는 질문
같은 값이 보이는데 왜 #N/A가 발생하나요?
숫자와 텍스트의 형식 차이, 앞뒤 공백, 검색 범위 누락을 먼저 확인하세요. 셀에 보이는 글자가 같아도 내부 값이 동일하지 않을 수 있습니다.
VLOOKUP 대신 XLOOKUP을 사용하면 오류가 사라지나요?
XLOOKUP은 검색 방향이 자유롭고 기본값이 정확한 일치이므로 일부 수식 구조를 단순화합니다. 하지만 원본에 값이 없거나 형식이 다르면 여전히 조회에 실패할 수 있습니다. 버전별 지원 여부도 확인해야 합니다.
마무리: 수식을 바꾸기 전에 데이터를 확인하세요
엑셀 VLOOKUP #N/A 오류는 대부분 조회값, 조회 범위, 데이터 형식, 공백, 일치 옵션을 확인하는 과정에서 원인을 찾을 수 있습니다. 처음부터 복잡한 함수를 추가하지 말고 한 셀에서 정상 조회되는 조건을 만든 뒤 나머지 행에 적용하는 방식이 좋습니다.
특히 여러 사람이 편집한 파일이나 외부 시스템에서 내려받은 자료는 코드의 형식과 공백부터 점검해 보세요. 정확한 원인을 해결해야 다음 정산표나 보고서에서도 같은 오류를 줄일 수 있습니다.
만약 엑셀 VLOOKUP #N/A오류가 궁금하시면 아래 글도 참고해 보세요!
2026.11.10 - [엑셀·스프레드시트] - 엑셀 VLOOKUP #N/A 오류가 발생하는 원인과 해결 방법
참고 자료: Microsoft 지원 - VLOOKUP 함수, Microsoft 지원 - #N/A 오류 수정 방법