VLOOKUP #N/A 오류 원인 5가지, 해결 순서부터 확인하세요
거래처 코드표에 VLOOKUP을 걸었는데 눈으로 보기엔 똑같은 값인데도 #N/A만 뜨는 경험, 엑셀로 명단을 매칭해 본 사람이라면 한 번쯤 겪었을 것이다.
예를 들어 A시트에는 거래처코드가 숫자 1001로 저장돼 있고 B시트에는 같은 코드가 텍스트 “1001”로 들어 있다면, 화면에는 똑같이 1001로 보여도 VLOOKUP은 서로 다른 값으로 인식해 바로 #N/A를 돌려준다.
이 글은 처음으로 VLOOKUP 결과에 #N/A를 마주한 사람 기준으로, 원인을 하나씩 좁혀가는 순서로 정리했다.
결론부터 말하면 VLOOKUP #N/A 오류는 수식이 틀려서가 아니라 거의 항상 데이터 형식이나 숨은 공백 때문에 생긴다.
직접 300줄 안팎의 거래처 코드표에 수식을 걸어 테스트해 보면, #N/A가 뜬 행의 절반 이상이 ‘숫자처럼 보이는데 실제로는 텍스트’인 경우였고, 나머지는 셀 끝에 스페이스가 하나 더 들어가 있거나 다른 시스템에서 복사해 온 값에 줄바꿈 문자가 섞여 있는 경우였다.
이 글을 끝까지 보면 원인 5가지, 단계별 해결 순서 5단계, 그래도 안 풀릴 때 쓰는 체크 표, 실제 사례로 살펴보는 해결 과정, IFERROR·XLOOKUP 같이 알아두면 좋은 대체 수식까지 한 번에 확인할 수 있다.
VLOOKUP에서 #N/A 오류가 뜨는 이유는 무엇일까요
VLOOKUP #N/A는 찾는 값과 완전히 같은 형태의 값이 원본 범위에 없다는 뜻이다.
마이크로소프트 공식 지원 문서에서도 #N/A는 VLOOKUP·XLOOKUP·MATCH 같은 조회 함수가 참조값을 찾지 못했을 때 나타나는 오류라고 설명한다(Microsoft 지원 문서). 실제 업무 표에서 자주 보이는 원인은 아래 다섯 가지로 좁혀진다.
| 원인 | 흔한 증상 |
|---|---|
| 텍스트·숫자 불일치 | 코드가 한쪽은 숫자, 한쪽은 텍스트로 저장돼 있음 |
| 보이지 않는 공백 | 셀 앞뒤에 공백이나 줄바꿈 문자가 숨어 있음 |
| 일치 옵션 누락 | 네 번째 인수를 빼먹거나 TRUE(근사값)로 둠 |
| 범위 고정 안 함 | 수식을 복사하면서 참조 범위가 밀려남 |
| 찾는 열이 첫 열 아님 | VLOOKUP은 찾는 값이 테이블 맨 왼쪽 열에 있어야 함 |
이 중 실무에서 가장 자주 보이는 건 역시 텍스트·숫자 불일치와 보이지 않는 공백이다.
ERP나 회계 프로그램에서 내보낸 파일은 코드 열을 통째로 텍스트로 저장하는 경우가 많은데, 반면 직접 타이핑한 표는 숫자로 자동 인식되기 때문에 두 표를 합칠 때 꼭 한 번씩 부딫히게 된다.
웹페이지나 메신저에서 복사해 붙여넣은 값에는 일반 스페이스가 아니라 CHAR(160) 같은 특수 공백이나 줄바꿈 문자가 섞여 들어가는 일도 흔한데, 이런 문자는 눈으로는 전혀 구분되지 않는다.
VLOOKUP #N/A 오류, 어떻게 고쳐야 할까요
형식 통일, 공백 제거, 일치 옵션 확인, 범위 고정 순서로 점검하면 대부분 해결된다.
스크린샷 없이도 따라 할 수 있도록 메뉴 경로와 수식을 같이 적었다.
- 데이터 형식 통일하기: 홈 탭 > 표시 형식에서 조회 열과 기준 열의 형식(텍스트/숫자)을 맞춘다. 숫자를 텍스트로 바꿀 땐
=TEXT(A1,"0")를, 반대로 텍스트를 숫자로 바꿀 땐 데이터 탭의 ‘텍스트 나누기’ 기능이나=VALUE(A1)를 쓴다. - 숨은 공백 제거하기: 비어 있는 것처럼 보이는 셀도
=TRIM(A1)또는=CLEAN(A1)으로 감싸 공백과 줄바꿈을 지운다. 다만 TRIM은 일반 스페이스만 지우기 때문에 웹에서 복사한 CHAR(160) 같은 특수 공백은=SUBSTITUTE(A1,CHAR(160),"")를 한 번 더 걸어야 지워진다. - 일치 옵션을 FALSE(0)으로 지정:
=VLOOKUP(찾는값, 범위, 열번호, FALSE)형태로 네 번째 인수를 반드시 적는다. 이 인수를 생략하면 기본값인 TRUE(근사 일치)로 동작해, 정렬되지 않은 표에서는 전혀 다른 값을 가져오거나 엉뚱하게 #N/A가 뜬다. - 범위를 절대참조로 고정: 범위를
$B$2:$D$100처럼 $ 기호로 고정한 뒤 F4키를 눌러 수식을 복사한다. 상대참조 그대로 복사하면 아래로 내려갈수록 범위가 같이 밀려 뒤쪽 행에서부터 #N/A가 나기 시작한다. - 참조 범위 다시 확인하기: 수식 탭 > 정의된 이름 > 이름 관리자에서 참조 범위가 밀리지 않았는지 점검한다. 원본 표에서 행을 삭제하거나 정렬을 바꾸면 이름으로 지정된 범위가 의도와 다르게 줄어드는 경우가 있다.
주의할 점은 1번과 2번을 고쳤는데도 여전히 안 된다면, 수정한 값이 실제로 저장됐는지부터 다시 봐야 한다는 것이다.
TRIM이나 TEXT 수식으로 보조 열만 만들어두고 VLOOKUP은 여전히 원본 열을 참조하게 두면 오류가 그대로 남는다.
보조 열에서 만든 값을 복사한 뒤 원본 자리에 ‘값으로 붙여넣기’로 덮어써야 VLOOKUP도 바뀐 값을 읚는다. 직접 해 보면 1번과 2번만 손봐도 #N/A의 절반 이상은 사라진다. 나머지는 범위나 옵션 문제인 경우가 많았다.
그래도 안 풀리면, 어떤 원인부터 확인해야 할까요
형식을 바꿔도 안 된다면 원본 데이터에 그 값이 실제로 있는지부터 Ctrl+F로 확인한다.
| 상황 | 확인 방법 | 해결 |
|---|---|---|
| 분명히 똑같아 보이는데 안 찾아짐 | 수식 입력줄에서 실제 값 확인 | CHAR(160) 같은 특수 공백이면 TRIM·CLEAN으로 제거 |
| 숫자인데 셀이 왼쪽 정렬돼 있음 | 셀 서식이 텍스트인지 확인 | =VALUE() 또는 데이터 탭 > 텍스트 나누기로 숫자 변환 |
| 범위는 맞는데 값이 안 보임 | 원본 표에 실제로 값이 있는지 Ctrl+F로 검색 | 오타·삭제된 행이면 원본 데이터 수정 |
| 이상한 값이 반환됨 | 세 번째 인수(열 번호)가 범위를 벗어났는지 확인 | 열 번호를 테이블 안쪽 숫자로 수정 |
| 수식을 복사하니 다 깨짐 | 범위가 절대참조($)인지 확인 | $ 고정 후 다시 채우기 |
이 표에서 가장 자주 놓치는 건 세 번째 줄, 즉 원본 표에 값이 실제로 있는지 확인하는 과정이다.
거래처가 폐업하거나 명단에서 빠진 코드를 조회하면 수식이 아무리 정확해도 #N/A가 뜨는 게 당연한데, 이건 수식 문제가 아니라 데이터 누락이라서 IFERROR로 가려주는 것 외에는 수식만으로 해결할 방법이 없다.
엑셀 오류든 윈도우 프로그램 오류든 원인을 하나씩 좁혀가는 방식은 비슷하다. 윈도우에서 프로그램 설치가 안 될 때도 원인별로 확인하는 순서를 따라가면 빠르게 풀린다.
실제 거래처 코드표 사례로 비교하면 어떻게 다를까요
말로만 설명하면 감이 잘 안 오니, 실제로 자주 마주치는 세 가지 패턴을 표로 정리했다. 거래처코드를 기준으로 VLOOKUP을 돌렸을 때 흔히 나오는 겉보기 증상과 실제 원인, 해결책을 한 줄씩 짝지은 것이다.
| A시트 값 | B시트 값 | 겉보기 | 실제 원인 | 해결 |
|---|---|---|---|---|
| 1001(숫자) | “1001”(텍스트) | 똑같아 보임 | 형식 불일치 | TEXT 함수로 숫자를 텍스트로 변환 후 비교 |
| “서울지점 “(끝 공백) | “서울지점” | 구분 안 됨 | 보이지 않는 끝 공백 | TRIM으로 공백 제거 후 값으로 붙여넣기 |
| “A-001” | “A-01” | 비슷해 보임 | 원본 데이터 자체의 오타 | 원본 코드 재확인, 당장은 IFERROR로 임시 처리 |
이 중 가장 많이 놓치는 패턴은 세 번째다. 수식도 맞고 형식도 같은데 안 찾아진다면, 애초에 두 표의 코드 자체가 다르게 입력됐을 가능성을 의심해야 한다.
이런 경우는 수식을 아무리 고쳐도 해결되지 않고, 두 표를 나란히 띄워 눈으로 대조하거나 담당자에게 원본을 다시 받아 고쳐야 한다.
IFERROR와 XLOOKUP, 같이 알아두면 왜 좋을까요
오류 자체를 없앨 순 없어도 IFERROR로 화면에 보이는 값을 바꾸고, XLOOKUP으로 더 유연하게 찾을 수 있다.
=IFERROR(VLOOKUP(...), "없음")처럼 쓰면 #N/A 대신 원하는 문구가 뜬다. 다만 이건 원인을 고치는 게 아니라 화면에 보이는 값만 가려주는 방식이라는 점은 기억해야 한다.
여러 코드표를 순서대로 조회해야 할 때는 =IFERROR(VLOOKUP(A1,표1,2,FALSE),IFERROR(VLOOKUP(A1,표2,2,FALSE),"미등록"))처럼 중첩해서, 1차 표에서 못 찾으면 2차 표를 다시 찾도록 만들 수도 있다.
최신 Microsoft 365 엑셀에는 VLOOKUP보다 유연한 XLOOKUP이 포함돼 있다.
=XLOOKUP(찾는값, 찾을범위, 반환범위, "대체값") 형태로 못 찾았을 때 반환할 값을 직접 지정할 수 있고, 찾는 열이 꼭 첫 번째가 아니어도 된다.
다만 XLOOKUP은 Microsoft 365와 Excel 2021 이상에서만 지원되고, Excel 2019 이하나 구버전 파일을 받는 쪽에서는 함수 자체가 인식되지 않아 #NAME? 오류로 바뀐다는 점도 함께 기억해 두면 좋다.
| 함수 | 왼쪽 열 조회 | 못 찾았을 때 |
|---|---|---|
| VLOOKUP | 안 됨(첫 열만 가능) | #N/A 발생, IFERROR로 감싸야 함 |
| INDEX/MATCH | 가능 | #N/A 발생, 구버전에도 사용 가능 |
| XLOOKUP | 가능 | 대체값을 수식 안에서 바로 지정 가능 |
직접 두 수식을 다 걸어 보면, 자료가 자주 바뀌는 표에는 XLOOKUP 쪽이 수식 관리가 확실히 편했다. 다만 예전 버전 엑셀과 파일을 주고받는다면 호환을 위해 VLOOKUP이나 INDEX/MATCH를 유지하는 게 안전하다.
외부와 파일을 자주 주고받는 환경이라면 INDEX/MATCH를 기본값으로 쓰고, 사내에서만 도는 파일에는 XLOOKUP을 쓰는 식으로 나눠 쓰는 것도 방법이다.
공백 제거용 함수도 상황에 맞게 골라 쓰면 좋다.
| 함수 | 역할 |
|---|---|
| TRIM | 양쪽 끝 공백과 중복 공백 제거 |
| CLEAN | 눈에 안 보이는 특수문자 제거 |
| SUBSTITUTE | CHAR(160) 같은 특정 문자를 지정해 제거 |
와이파이가 갑자기 안 잡힐 때도 비슷하게 원인을 하나씩 좁혀가면 되는데, 원인별 확인 순서를 정리해둔 글도 참고할 만하다.
자주 묻는 질문
VLOOKUP과 XLOOKUP, 지금 당장 바꿔야 하나요
아니다. 기존 파일이 구버전 호환이 필요하면 VLOOKUP을 유지하고, 새로 만드는 표라면 XLOOKUP이 더 편하다.
IFERROR를 쓰면 #N/A 원인이 고쳐지나요
아니다. IFERROR는 화면에 보이는 값만 바꿔줄 뿐, 데이터 자체의 형식이나 공백 문제는 그대로 남는다.
찾는 값이 왼쪽 열에 있어도 VLOOKUP을 쓸 수 있나요
기본 VLOOKUP은 안 된다. 찾는 열이 왼쪽에 있으면 INDEX/MATCH나 XLOOKUP을 쓰는 게 맞다.
정리하면 VLOOKUP #N/A 오류는 수식이 잘못된 게 아니라 거의 항상 데이터 형식이나 공백 문제다.
형식 통일, 공백 제거, FALSE 옵션, 범위 고정, 참조 범위 재확인까지 다섯 단계를 순서대로 점검하면 대부분 바로 풀리고, 그래도 안 풀린다면 원본 데이터에 값이 실제로 존재하는지부터 다시 확인하면 된다.
다음에는 엑셀에서 자주 겪는 다른 수식 오류도 정리해볼 예정이다. 여러분은 #N/A를 주로 어떤 상황에서 만나시나요? 댓글로 알려주시면 다음 글에 참고하겠다.
출처: Editlab, https://editlab.luvpp.com
EDITLAB이 매체는 Editlab이 발행합니다분야별 전문 매체 8곳과 무료 데이터 도구를 함께 운영합니다.네트워크 보기 ›
글 Editlab 편집부 · 발행 전 자동 점검(중복·형식·규칙) · 정정 요청 · Google 검색에서 이 매체를 우선 출처로 등록
한 번 잘못하면 되돌리기 번거로운 것들이 있습니다. 겪어 본 사람에게 직접 듣는 편이 빠를 때가 있습니다. 글은 누구나 읽을 수 있고, 질문을 남기려면 카페 가입이 필요합니다.
EDITLAB 뉴스레터
오늘의 한 가지
평일 아침, 오늘 꼭 알아야 할 정보 하나. 신청 마감이 코앞인 지원금, 검색해도 안 나오는 실제 절차와 후기.
생년월일을 넣으면 매주 월요일 내 주간 운세도 같이 받을 수 있습니다.
개인정보처리방침 · 문의 editlab204@gmail.com · 발행 Editlab
새 글 알림 받기
유용한 정보가 올라올 때 이메일로 알려드립니다.
