엑셀에서 VLOOKUP 함수를 사용하다 보면 분명히 표 안에 있는 값인데 결과가 #N/A로 표시되는 경우가 있습니다.
예를 들어 상품코드 A102를 입력했고 원본 표에도 A102가 있는데 결과만 #N/A가 나오는 식입니다.
이럴 때 많은 사람이 가장 먼저 다음처럼 수식을 바꿉니다.
=IFERROR(VLOOKUP(...),"")
화면에서 오류는 사라집니다.
하지만 실제 원인은 해결되지 않았을 수 있습니다.
Microsoft는 #N/A 오류를 기본적으로 수식이 찾으라고 지정한 값을 찾지 못했다는 의미로 설명합니다.
따라서 VLOOKUP 오류에서는 오류를 숨기는 것보다
조회값이 실제로 존재하는가
↓
값의 형식이 같은가
↓
눈에 보이지 않는 공백이나 문자 차이가 있는가
를 먼저 확인하는 것이 중요합니다.
먼저 VLOOKUP 수식 구조부터 확인합니다
예를 들어 다음 수식이 있다고 가정해 보겠습니다.
=VLOOKUP(E2,$A$2:$C$100,3,FALSE)
각 부분의 의미는 다음과 같습니다.
E2
찾을 값입니다.
$A$2:$C$100
값을 찾을 표입니다.
3
찾은 행에서 세 번째 열의 값을 가져오라는 뜻입니다.
FALSE
정확하게 같은 값을 찾으라는 뜻입니다.
여기서 FALSE를 사용했는데 정확히 같은 값이 검색 범위 첫 번째 열에 없으면 #N/A가 발생할 수 있습니다.
따라서 오류가 발생했을 때는 함수 전체를 다시 만들기 전에 먼저 조회값과 원본 데이터를 비교합니다.
원인 1. 실제로 조회값이 원본 표에 없습니다
가장 단순하지만 가장 먼저 확인해야 하는 원인입니다.
다음 표가 있다고 가정해 보겠습니다.
| 상품코드 | 상품명 | 가격 |
|---|---|---|
| A101 | 키보드 | 39,000 |
| A102 | 마우스 | 25,000 |
| A103 | 모니터 | 240,000 |
| A105 | 스피커 | 59,000 |
그리고 E2 셀에
A104
가 입력되어 있습니다.
수식이
=VLOOKUP(E2,A2:C5,3,FALSE)
라면 결과는 #N/A입니다.
이유는 원본 표에 A104가 없기 때문입니다.
수식 자체는 잘못되지 않았습니다.
오히려 #N/A가 정상적인 결과입니다.
오류가 나오면 Ctrl+F로 먼저 검색해봅니다
값이 실제로 존재하는지 가장 빠르게 확인하는 방법 중 하나는 찾기 기능입니다.
Windows Excel에서는
Ctrl + F
를 누르고 조회값을 직접 검색합니다.
예를 들어
A104
를 검색했는데 아무 결과도 없다면 VLOOKUP이 못 찾는 것이 이상한 상황이 아닙니다.
반대로 검색 결과가 나오는데 VLOOKUP만 #N/A라면 다음 원인을 확인합니다.
원인 2. 숫자처럼 보여도 한쪽은 ‘텍스트’일 수 있습니다
실무에서 매우 자주 발생하는 문제입니다.
화면에는 둘 다
1001
이라고 보이는데 Excel 내부에서는 서로 다른 값으로 인식될 수 있습니다.
예를 들어
조회 셀 E2의 1001은 숫자
원본 A2의 1001은 텍스트
로 저장되어 있을 수 있습니다.
사람 눈에는 같습니다.
하지만 정확히 일치하는 값을 찾는 과정에서는 형식 차이 때문에 원하는 결과가 나오지 않을 수 있습니다.
숫자와 텍스트를 빠르게 구분하는 방법
기본 설정에서는 숫자와 텍스트의 정렬이 다르게 보일 수 있습니다.
일반적으로 숫자는 오른쪽, 텍스트는 왼쪽 정렬되는 경우가 많습니다.
하지만 사용자가 정렬을 변경했을 수도 있으므로 이것만으로 확정하면 안 됩니다.
좀 더 명확하게 확인하려면 ISTEXT와 ISNUMBER 함수를 사용할 수 있습니다.
예를 들어 A2가 숫자인지 확인하려면
=ISNUMBER(A2)
텍스트인지 확인하려면
=ISTEXT(A2)
를 입력합니다.
결과가 TRUE 또는 FALSE로 표시됩니다.
실제 비교 예시
A2에는 숫자 1001이 있습니다.
B2에는 문자 형태의 "1001"이 있습니다.
다음처럼 확인할 수 있습니다.
| 셀 | 표시값 | ISNUMBER | ISTEXT |
|---|---|---|---|
| A2 | 1001 | TRUE | FALSE |
| B2 | 1001 | FALSE | TRUE |
겉으로는 같아 보여도 Excel에서는 데이터 유형이 다릅니다.
텍스트 숫자를 숫자로 바꾸는 방법
텍스트로 저장된 숫자는 여러 방법으로 변환할 수 있습니다.
방법 1. 오류 표시 메뉴 사용
셀 왼쪽 위에 녹색 삼각형이 표시되면서
숫자가 텍스트로 저장됨
경고가 나타난다면 경고 버튼을 눌러
숫자로 변환
을 선택할 수 있습니다.
방법 2. VALUE 함수 사용
예를 들어 A2가 텍스트 숫자라면
=VALUE(A2)
로 숫자 형태로 변환할 수 있습니다.
방법 3. 1을 곱하는 방식
보조 열에서
=A2*1
처럼 계산해 숫자로 바꾸는 방법도 있습니다.
다만 원본 데이터 구조를 바꾸기 전에 복사본에서 테스트하는 것이 좋습니다.
반대로 상품코드는 숫자로 바꾸면 안 될 수도 있습니다
모든 숫자 모양 데이터를 숫자로 바꾸는 것이 정답은 아닙니다.
예를 들어 상품코드가
00125
라면 숫자로 변환할 경우
125
가 될 수 있습니다.
우편번호나 관리번호처럼 앞의 0이 의미가 있는 데이터도 마찬가지입니다.
따라서 먼저 생각해야 합니다.
이 값은 실제 숫자인가, 아니면 숫자로 이루어진 코드인가?
계산할 값이라면 숫자가 적절할 수 있습니다.
식별번호라면 텍스트로 통일하는 편이 더 적절할 수 있습니다.
원인 3. 눈에 보이지 않는 공백이 들어 있습니다
다음 두 값은 화면에서는 거의 같아 보입니다.
A102
A102
하지만 두 번째 값 뒤에는 공백이 하나 있습니다.
사람이 눈으로 확인하기는 쉽지 않습니다.
특히 다른 시스템에서 복사한 데이터, CSV 파일, 웹사이트 표, ERP에서 내보낸 자료에서는 앞뒤 공백이 포함되는 경우가 있습니다.
VLOOKUP에서 정확한 일치를 사용한다면 이런 차이가 문제를 만들 수 있습니다.
LEN 함수로 글자 수를 비교하면 찾기 쉽습니다
눈으로 구분하기 어려운 공백은 LEN 함수로 글자 수를 비교하면 확인하기 쉽습니다.
A2에는 정상 값
A102
가 있고,
B2에는 뒤에 공백이 포함된
A102
가 있다고 가정해 보겠습니다.
다음 함수를 입력합니다.
=LEN(A2)
=LEN(B2)
결과는 예를 들어 다음처럼 나타날 수 있습니다.
| 셀 | 보이는 값 | LEN 결과 |
|---|---|---|
| A2 | A102 | 4 |
| B2 | A102 | 5 |
눈에는 거의 같아 보여도 문자 수가 다릅니다.
이런 경우 공백이나 보이지 않는 문자가 들어 있는지 의심할 수 있습니다.
일반적인 앞뒤 공백은 TRIM 함수로 정리할 수 있습니다
예를 들어 A2의 텍스트 앞이나 뒤에 불필요한 공백이 있다면
=TRIM(A2)
를 사용해 정리할 수 있습니다.
원본 데이터가 수천 행이라면 직접 한 셀씩 수정하기보다 보조 열을 만들어 TRIM 결과를 확인한 뒤 값을 정리하는 것이 안전합니다.
예를 들어
A열: 원본 상품코드
B열: 정리된 코드
라면 B2에
=TRIM(A2)
를 입력하고 아래로 채웁니다.
이후 결과가 정상인지 확인합니다.
TRIM으로 해결되지 않는 문자도 있을 수 있습니다
웹사이트나 외부 시스템에서 복사한 데이터에는 일반 공백과 다른 특수 공백이나 제어 문자가 포함될 수 있습니다.
이런 경우에는 단순히 TRIM만 적용해도 해결되지 않을 수 있습니다.
따라서
LEN 값은 다른데 TRIM 후에도 차이가 난다
면 외부 데이터에 다른 문자가 포함됐을 가능성을 확인해야 합니다.
필요한 경우 CLEAN 함수 등으로 제어 문자를 정리하는 방법을 검토할 수 있습니다.
다만 원본 데이터를 한 번에 덮어쓰기보다는 보조 열에서 결과를 확인하는 편이 안전합니다.
세 가지 원인을 한 번에 구분하는 진단표
VLOOKUP에서 #N/A가 나오면 아래 순서대로 확인해 볼 수 있습니다.
| 확인 | 테스트 방법 | 결과 |
|---|---|---|
| 값 존재 여부 | Ctrl+F | 없으면 원본 데이터 확인 |
| 숫자/텍스트 | ISNUMBER, ISTEXT | 데이터 형식 통일 |
| 숨은 공백 | LEN | 글자 수 차이 확인 |
| 앞뒤 공백 | TRIM | 정리 후 재검색 |
| 수식 일치 방식 | FALSE 확인 | 정확히 일치 검색 |
| 범위 첫 열 | table_array 확인 | 조회값 열 위치 확인 |
이 표만 기억해도 무작정 수식을 다시 입력하는 시간을 줄일 수 있습니다.
VLOOKUP에서 FALSE를 빼면 왜 위험할 수 있을까?
다음 두 수식은 다릅니다.
=VLOOKUP(E2,A2:C100,3,FALSE)
=VLOOKUP(E2,A2:C100,3)
첫 번째는 정확한 일치를 찾습니다.
두 번째는 마지막 인수를 생략했습니다.
Microsoft 공식 안내에 따르면 VLOOKUP의 마지막 인수를 TRUE로 지정하거나 생략하면 근사값 검색을 사용합니다.
이 경우 첫 번째 열이 올바르게 정렬되어 있어야 합니다.
정렬되지 않은 상태에서는 예상하지 못한 값이 반환될 수 있습니다.
상품코드·사번 검색이라면 보통 정확한 일치가 필요합니다
예를 들어 다음과 같은 데이터를 찾는 경우입니다.
- 상품코드
- 사번
- 주문번호
- 회원번호
- 부품번호
- 거래처 코드
이 값들은 비슷한 값을 찾는 것이 아니라 정확히 같은 값을 찾아야 하는 경우가 많습니다.
따라서 이런 검색에서는 일반적으로
FALSE
를 명시하는 것이 이해하기 쉽습니다.
예:
=VLOOKUP(F2,$A$2:$D$1000,4,FALSE)
TRUE는 무조건 잘못된 옵션이 아닙니다
TRUE가 오류라는 뜻은 아닙니다.
근사값 검색이 필요한 상황에서는 사용할 수 있습니다.
예를 들어 점수에 따라 등급을 찾는 표가 있다고 가정해 보겠습니다.
| 기준점수 | 등급 |
|---|---|
| 0 | D |
| 60 | C |
| 70 | B |
| 80 | A |
75점에 해당하는 등급을 찾는다면 정확히 75라는 행이 없어도 기준에 따라 가장 가까운 하위 구간을 찾는 방식이 필요할 수 있습니다.
이런 상황에서 근사값 검색이 사용될 수 있습니다.
단, Microsoft는 근사값 검색을 사용할 때 조회표 첫 번째 열의 정렬 상태가 중요하다고 안내합니다.
검색 범위의 첫 번째 열도 반드시 확인합니다
VLOOKUP에는 중요한 구조적 제한이 있습니다.
조회값은 지정한 table_array의 첫 번째 열에서 찾습니다.
예를 들어 표가 다음과 같습니다.
| A열 상품명 | B열 상품코드 | C열 가격 |
|---|---|---|
| 마우스 | A102 | 25,000 |
| 키보드 | A103 | 39,000 |
상품코드 A102를 찾고 싶은데 수식이
=VLOOKUP(E2,A2:C100,3,FALSE)
라면 VLOOKUP은 A열의 상품명에서 A102를 찾습니다.
당연히 찾지 못합니다.
이 경우 조회 범위를 B열부터 잡는 등 구조를 다시 확인해야 합니다.
범위를 잘못 잡으면 값이 있어도 #N/A가 나올 수 있습니다
상품코드가 B열에 있다면 예를 들어
=VLOOKUP(E2,B2:C100,2,FALSE)
처럼 검색 범위의 첫 번째 열이 상품코드가 되도록 설정할 수 있습니다.
따라서 오류가 발생하면
조회값이 어느 열에 있는가
뿐 아니라
수식의 검색 범위가 어느 열에서 시작하는가
도 확인합니다.
행을 추가한 뒤 갑자기 #N/A가 생겼다면 범위도 확인합니다
처음에는 정상적으로 작동했던 수식이 새 데이터를 추가한 뒤부터 오류가 발생할 수 있습니다.
예를 들어 기존 수식이
=VLOOKUP(E2,$A$2:$C$100,3,FALSE)
인데 새 데이터가 101행 이후에 추가되었다고 가정해 보겠습니다.
새 상품코드가 A120에 있어도 검색 범위는 A100까지만 지정되어 있습니다.
따라서 VLOOKUP은 새 데이터를 검색하지 않습니다.
이 경우 범위를 수정하거나 데이터를 Excel 표 형식으로 관리하는 방법을 검토할 수 있습니다.
#N/A를 IFERROR로 바로 숨기면 안 되는 이유
다음 수식은 자주 사용됩니다.
=IFERROR(VLOOKUP(E2,A2:C100,3,FALSE),"")
오류가 발생하면 빈 셀처럼 표시됩니다.
보고서를 깔끔하게 만들 때는 유용합니다.
하지만 문제 진단 단계에서는 주의해야 합니다.
Microsoft도 오류 처리 함수로 #N/A를 다른 값으로 바꾸는 것은 가능하지만, 먼저 수식이 의도대로 작동하는지 확인하는 것이 중요하다고 안내합니다.
예를 들어 상품코드가 실제로 있는데 형식 오류 때문에 검색되지 않는 상황에서 IFERROR를 사용하면
잘못된 데이터가 단순한 빈칸으로 보일 수 있습니다.
IFERROR는 원인을 해결한 다음 사용합니다
권장 순서는 다음과 같습니다.
1. #N/A 상태에서 원인 확인
↓
2. 조회값과 원본 비교
↓
3. 데이터 형식·공백 수정
↓
4. 정상 조회 확인
↓
5. 필요한 경우 IFERROR 적용
예를 들어 정상 작동을 확인한 뒤
=IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"미등록")
처럼 사용하면 실제 미등록 데이터를 사용자에게 더 읽기 쉽게 표시할 수 있습니다.
‘값이 없어서 #N/A’와 ‘데이터가 잘못돼서 #N/A’를 구분합니다
실무에서는 이 두 경우를 구분하는 것이 중요합니다.
정상적인 #N/A
조회값이 원본에 실제로 없음
예:
신규 상품코드가 아직 기준표에 등록되지 않음
수정해야 할 #N/A
원본에 값은 있지만 형식이나 공백 때문에 검색 실패
예:
A102와 A102
또는
숫자 1001과 텍스트 "1001"
오류 표시가 같다고 원인도 같은 것은 아닙니다.
대량 데이터라면 보조 열로 원인을 표시하면 빠릅니다
수백 개의 #N/A를 하나씩 확인하기 어렵다면 보조 열을 만들 수 있습니다.
예를 들어 조회값이 E2에 있다면
=LEN(E2)
=ISTEXT(E2)
=ISNUMBER(E2)
같은 함수를 옆 열에서 사용합니다.
원본 값에도 같은 검사를 적용합니다.
그러면 다음처럼 비교할 수 있습니다.
| 조회값 | LEN | 형식 | 원본값 | LEN | 형식 |
|---|---|---|---|---|---|
| A102 | 4 | 텍스트 | A102 | 4 | 텍스트 |
| A103 | 4 | 텍스트 | A103(공백) | 5 | 텍스트 |
| 1004 | 4 | 숫자 | 1004 | 4 | 텍스트 |
이렇게 보면 어떤 행이 단순 미등록이고 어떤 행이 데이터 형식 문제인지 훨씬 빨리 찾을 수 있습니다.
VLOOKUP 대신 XLOOKUP을 써야 할까?
Microsoft는 현재 지원되는 최신 Excel에서는 XLOOKUP을 VLOOKUP의 개선된 대안으로 안내하고 있습니다.
XLOOKUP은 기본적으로 정확한 일치를 사용하고, VLOOKUP처럼 검색값이 범위의 첫 번째 열에 있어야 한다는 제한도 줄어듭니다.
예를 들어
=XLOOKUP(E2,A2:A100,C2:C100)
처럼 조회 범위와 반환 범위를 따로 지정할 수 있습니다.
하지만 XLOOKUP으로 바꾼다고 데이터 자체의 오류가 사라지는 것은 아닙니다.
조회값에 숨은 공백이 있거나 숫자와 텍스트 형식이 다르다면 여전히 원하는 결과가 나오지 않을 수 있습니다.
따라서 기존 파일의 #N/A를 해결하려는 목적이라면 함수 교체보다 먼저 데이터 상태를 확인합니다.
VLOOKUP #N/A 오류 점검 순서
실제 업무에서는 아래 순서대로 보면 빠릅니다.
1단계
오류가 발생한 조회값을 복사합니다.
2단계
Ctrl+F로 원본 표에서 직접 검색합니다.
3단계
값이 없다면 기준 데이터에 실제로 등록되어야 하는 값인지 확인합니다.
4단계
값이 있다면 ISTEXT, ISNUMBER로 형식을 비교합니다.
5단계
LEN으로 문자 수를 비교합니다.
6단계
불필요한 앞뒤 공백이 있다면 TRIM을 검토합니다.
7단계
VLOOKUP의 마지막 인수가 FALSE인지 확인합니다.
8단계
검색 대상 열이 table_array의 첫 번째 열인지 확인합니다.
9단계
검색 범위가 새로 추가한 행까지 포함하는지 확인합니다.
10단계
정상적으로 검색되는 것을 확인한 뒤 필요한 경우 IFERROR를 적용합니다.
30초 진단표
| 증상 | 가장 먼저 볼 것 |
|---|---|
| 특정 코드 하나만 #N/A | 원본에 값 존재 여부 |
| 값이 있는데 #N/A | 숫자·텍스트 형식 |
| 복사한 데이터에서 주로 오류 | 공백·숨은 문자 |
| 새 행부터 오류 | VLOOKUP 범위 |
| 엉뚱한 값이 반환됨 | TRUE·FALSE 설정 |
| 값이 오른쪽 열에 있음 | 검색 범위 첫 열 |
| IFERROR 때문에 빈칸만 보임 | IFERROR 제거 후 원래 오류 확인 |
자주 하는 실수
1. #N/A가 뜨자마자 IFERROR 사용
오류를 숨길 뿐 데이터 문제를 해결하지 못할 수 있습니다.
2. 화면에 같은 숫자니까 같은 값이라고 판단
숫자와 텍스트 숫자는 내부 형식이 다를 수 있습니다.
3. 공백은 눈으로만 확인
보이지 않는 공백은 LEN으로 확인하는 편이 빠릅니다.
4. 마지막 인수를 생략
정확한 코드 검색인데 근사값 검색을 사용할 수 있습니다.
5. 조회 범위를 확인하지 않음
새 데이터가 기존 범위 밖에 있으면 찾을 수 없습니다.
6. XLOOKUP으로 바꾸면 모든 문제가 해결된다고 생각
데이터 자체의 공백·형식 문제는 함수만 바꿔도 그대로 남을 수 있습니다.
마무리
VLOOKUP에서 #N/A가 표시되면 먼저 수식을 다시 작성할 필요는 없습니다.
오류의 의미부터 확인하면 됩니다.
Microsoft의 공식 설명처럼 #N/A는 기본적으로 수식이 조회하려는 값을 찾지 못했다는 신호입니다.
그렇다면 확인 순서도 단순합니다.
첫째, 값이 실제로 존재하는지 확인합니다.
둘째, 숫자와 텍스트 형식이 같은지 확인합니다.
셋째, 눈에 보이지 않는 공백이나 문자 차이가 있는지 확인합니다.
그다음 수식의 FALSE, 검색 범위의 첫 번째 열, 새로 추가된 데이터가 범위 안에 포함되어 있는지를 확인합니다.
특히 IFERROR는 오류를 해결하는 함수라기보다 결과를 보기 좋게 처리하는 함수에 가깝습니다.
따라서 원인을 확인하기 전에 오류부터 빈칸으로 숨기면 오히려 잘못된 데이터를 발견하기 어려워질 수 있습니다.
VLOOKUP 오류를 빠르게 해결하고 싶다면 복잡한 함수부터 공부하기보다
Ctrl+F → ISNUMBER·ISTEXT → LEN → TRIM → 수식 범위 확인
순서로 점검해 보세요.
값이 없는 문제인지, 데이터 형식 문제인지, 수식 범위 문제인지 훨씬 빠르게 구분할 수 있습니다.
※ 이 글은 Microsoft Excel의 VLOOKUP 함수와 #N/A 오류 해결 공식 안내를 기준으로 작성했습니다. Excel 버전에 따라 XLOOKUP 지원 여부와 일부 기능은 다를 수 있으며, 실제 업무 파일에서는 원본 데이터를 수정하기 전에 복사본이나 보조 열에서 먼저 결과를 확인하는 것이 좋습니다.
