VLOOKUP #N/A 오류, 결론부터 말하면 이렇게 고친다
VLOOKUP #N/A 오류는 열에 아홉은 찾는 값이 참조 범위 첫 번째 열에 정확히 없어서 생긴다. 숫자인데 텍스트로 저장돼 있거나, 앞뒤에 안 보이는 공백이 붙어 있거나, 범위를 아래로 내릴 때 참조가 같이 밀려버린 경우가 대부분이다.
실무에서 거래처 코드로 VLOOKUP을 걸었다가 데이터는 분명 있는데 계속 오류가 떠서 한참 헤맨 적이 있다. 원인은 엑셀 파일이 아니라 회계 프로그램에서 뽑아온 숫자가 텍스트 형식으로 저장돼 있었던 것이었다. 셀을 클릭해 보니 숫자가 왼쪽 정렬로 표시돼 있었고, 셀 왼쪽 위에 작은 초록 삼각형까지 붙어 있었다. =VALUE() 함수로 텍스트를 숫자로 바꿔주자 오류 300여 건이 한 번에 사라졌다.
이렇게 겉으로는 같은 오류라도 원인은 제각각이라, 순서를 정해두고 하나씩 점검하는 게 가장 빠르다. 급하면 아래 단계별 방법을 순서대로만 따라 해도 대부분 5분 안에 해결된다.
단계별로 따라 하는 VLOOKUP #N/A 오류 해결 방법
스크린샷 없이도 메뉴 경로만 보고 따라 할 수 있게 순서대로 정리했다. 위에서부터 확률이 높은 원인 순으로 배치했으니, 하나씩 지워가며 확인하면 된다.
- 참조 범위 고정 — 수식 안의 범위를 클릭한 뒤 키보드 F4를 눌러 $A$2:$D$100처럼 행과 열을 모두 고정한다. 예를 들어 =VLOOKUP(A2,B2:D100,3,FALSE)처럼 범위가 상대참조로 돼 있으면, 이 수식을 아래로 채우는 순간 참조 범위가 B3:D101, B4:D102 식으로 한 칸씩 밀려버려 뒤로 갈수록 엉뚱한 값을 찾거나 범위를 벗어나 오류가 난다. F4로 =VLOOKUP(A2,$B$2:$D$100,3,FALSE)로 고정하면 몇 줄을 채우든 범위가 그대로 유지된다.
- 네 번째 인수 확인 — 수식 맨 끝 인수가 비어 있거나 TRUE면 정확히 일치하는 값만 찾도록 FALSE 또는 0으로 바꾼다. TRUE(근사 일치)로 두면 데이터가 오름차순 정렬돼 있지 않을 때 엉뚱한 값을 가져오거나 #N/A가 뜬다. 근사 일치는 구간별 등급표처럼 정렬이 보장된 표에서만 써야 안전하다.
- 숫자·텍스트 서식 통일 — 리본 메뉴 홈 > 표시 형식에서 두 셀이 각각 숫자인지 텍스트인지 확인한다. 다르면 =VALUE() 또는 =TEXT()로 형식을 맞춘다. 특히 회계 프로그램이나 웹에서 내려받은 표는 숫자처럼 보여도 실제로는 텍스트인 경우가 잦으니 가장 먼저 의심할 항목이다.
- 숨은 공백 제거 — 찾는 값 앞뒤에 공백이 있는지 의심되면 =TRIM(찾을값)으로 감싸 공백부터 지운다. 웹페이지에서 복사해 붙여넣은 데이터는 줄바꿈 문자(개행)가 섞여 있을 때가 있는데, 이때는 TRIM만으로 안 지워지니 =CLEAN(TRIM(찾을값))처럼 두 함수를 같이 쓴다.
- 오류 검사 도구 실행 — 리본 메뉴 수식 > 수식 분석 > 오류 검사를 눌러 엑셀이 자동으로 다음 오류 셀로 이동하게 한다. 오류 셀을 클릭하면 나오는 느낌표 아이콘의 계산 단계 표시 기능으로 어느 인수에서 값을 못 찾았는지 한 단계씩 확인할 수 있다.
다섯 단계를 순서대로 지워가며 확인하면, 표 전체를 처음부터 다시 만들지 않아도 오류가 난 지점만 정확히 짚어낼 수 있다.
그래도 안 될 때, 원인별 해결 표로 바로 확인
위 5단계를 다 해봤는데도 오류가 남아 있다면 아래 표에서 증상에 맞는 줄을 찾는다. 자주 놓치는 원인까지 포함해 정리했다.
| 증상 | 진짜 원인 | 해결법 |
|---|---|---|
| 값이 눈으로는 분명 있는데 오류 | 숫자·텍스트 형식 불일치 | =VALUE() 또는 =TEXT()로 형식 통일 |
| 표를 아래로 채우면 갑자기 오류 | 상대참조로 범위가 밀림 | F4로 절대참조 고정 |
| 정확히 같은 글자인데 오류 | 앞뒤 공백·줄바꿈 문자 | TRIM, CLEAN 함수로 정리 |
| 찾는 값이 표 중간 열에 있음 | VLOOKUP은 왼쪽 열만 검색 | INDEX+MATCH 또는 XLOOKUP 사용 |
| 일부 행만 오류 | 병합 셀·숨겨진 행 데이터 | 병합 해제 후 다시 채우기 |
| 파일을 열 때마다 계산이 안 됨 | 계산 옵션이 수동으로 설정됨 | 수식 > 계산 옵션 > 자동으로 변경 |
| 다른 파일 값을 참조할 때만 오류 | 원본 통합 문서가 닫혀 있음 | 원본 파일을 열어두거나 값으로 붙여넣기 |
같이 알아두면 좋은 팁
IFERROR는 원인 해결 후 마지막에만
=IFERROR(VLOOKUP(A2,$B$2:$D$100,3,FALSE),””)처럼 감싸면 화면에서 오류가 사라지긴 하지만, 원인을 안 고치고 먼저 씌우면 데이터가 실제로 빠진 건지 오류가 감춰진 건지 구분이 안 돼 나중에 집계 오류로 이어질 수 있다. 위 표로 원인부터 잡고, 정말로 값이 없는 게 정상인 행에만 마지막 단계에서 IFERROR를 씌우는 순서를 권한다.
표 중간 열을 찾을 땐 INDEX+MATCH
VLOOKUP은 구조상 찾는 값이 항상 참조 범위의 맨 왼쪽 열에 있어야 하는데, 실무 표는 이 조건을 못 맞추는 경우가 많다. 이럴 때는 =INDEX(D2:D100,MATCH(A2,B2:B100,0))처럼 INDEX와 MATCH를 조합하면 찾는 열이 기준 열보다 왼쪽에 있어도 문제없이 값을 가져온다. 표 중간에 열을 새로 삽입해도 참조가 깨지지 않는다는 장점도 있다.
최신 버전이면 XLOOKUP도 고려
마이크로소프트 엑셀 최신 버전인 Microsoft 365나 엑셀 2021 이상을 쓰고 있다면 XLOOKUP 함수가 VLOOKUP의 구조적 한계(왼쪽 열만 검색, 열 삽입 시 깨짐)를 아예 없애준다. 예를 들어 =XLOOKUP(A2,B2:B100,D2:D100,”값 없음”)처럼 쓰면 값을 못 찾았을 때 표시할 문구까지 네 번째 인수에서 바로 지정할 수 있어 별도로 IFERROR를 씌울 필요도 없다.
| 구분 | VLOOKUP | XLOOKUP |
|---|---|---|
| 검색 방향 | 항상 왼쪽 열만 | 양방향 모두 가능 |
| 열 삽입 시 안정성 | 범위 번호가 깨지기 쉬움 | 영향 없음 |
| 오류 값 지정 | IFERROR로 별도 처리 | 네 번째 인수로 바로 지정 |
| 지원 버전 | 모든 버전 | Microsoft 365, 엑셀 2021 이상 |
자주 묻는 질문
Q. VLOOKUP과 XLOOKUP 중 어떤 걸 써야 하나요?
마이크로소프트 365나 엑셀 2021 이상이라면 XLOOKUP이 더 안전하다. 왼쪽 열 검색 제약이 없고 오류 시 표시할 값도 함수 안에서 바로 지정할 수 있어, 원인 하나가 애초에 사라진다.
Q. IFERROR로 감싸면 오류 원인이 없어지나요?
아니다, IFERROR는 화면 표시만 바꿀 뿐 원인은 그대로 남는다. 먼저 원인별 해결 표로 점검한 뒤 마지막에만 씌우는 게 안전하다.
Q. 텍스트로 저장된 숫자인지 어떻게 확인하나요?
셀을 클릭했을 때 숫자가 기본적으로 오른쪽 정렬이면 숫자, 왼쪽 정렬이면 텍스트다. 셀 왼쪽 위 초록 삼각형 표시도 텍스트 저장 신호다.
Q. 근사 일치(TRUE)는 언제 써야 하나요?
기준값이 오름차순으로 정렬된 구간표, 예를 들어 점수대별 등급을 매길 때만 쓴다. 일반적인 코드·이름 조회에는 항상 FALSE를 써야 엉뚱한 값을 가져오는 걸 막을 수 있다.
오늘 소개한 5단계와 원인별 표만 순서대로 확인해도 오류는 대부분 그 자리에서 풀린다. 혹시 이 방법으로도 안 풀리는 경우가 있었다면 어떤 상황이었는지 댓글로 남겨 주시면 다음 글에서 다뤄보겠다.
Editlab, https://editlab.luvpp.com
EDITLAB이 매체는 Editlab이 발행합니다분야별 전문 매체 8곳과 무료 데이터 도구를 함께 운영합니다.네트워크 보기 ›
