VLOOKUP #N/A 오류는 대부분 조회값의 보이지 않는 공백, 데이터 형식 불일치, 정확히 일치 옵션 누락 중 하나가 원인이다.
거래처 코드표나 직원 명단처럼 수천 행을 매일 대조하는 사무직이라면 VLOOKUP 뒤에 뜨는 #N/A를 보고 당황한 경험이 한 번쯤 있을 것이다. 월간 매출표에서 거래처 코드를 대조하다 분명 같은 코드인데 #N/A가 떠서 한 시간 가까이 헤맨 적이 있었다.
알고 보니 원인은 거래처 시스템에서 엑셀로 내보낼 때 코드 뒤에 붙은 보이지 않는 공백이었다. 이런 식으로 원인을 순서대로 짚어가면 VLOOKUP #N/A 오류는 대부분 5분 안에 해결된다. 지금부터 점검 순서와 원인별 해결법을 표로 정리했다.
당시 대조하던 거래처 코드는 총 1,284행이었는데, 그중 37행에서만 매칭이 안 됐다.
오류가 난 셀을 하나씩 열어보니 코드 끝에 일반 공백이 아니라 시스템에서 자동으로 붙는 비표준 공백 문자가 섞여 있었고, TRIM만으로는 지워지지 않아 SUBSTITUTE로 해당 문자를 따로 제거해야 했다.
결론부터, VLOOKUP #N/A 오류 가장 빨리 고치는 법
가장 빠른 해결법은 수식 마지막 인수를 FALSE로 바꾸고 조회값에 TRIM을 씌우는 것이다.
예를 들어 =VLOOKUP(A2,B:C,2,TRUE)처럼 마지막 인수를 생략했거나 TRUE로 두었다면 =VLOOKUP(TRIM(A2),B:C,2,FALSE)로 바꿔본다. 현장에서 만나는 VLOOKUP #N/A 오류의 상당수는 이 두 가지만 손봐도 사라진다.
| 상황 | 원래 수식 | 수정 수식 |
|---|---|---|
| 정확히 일치 옵션 누락 | =VLOOKUP(A2,B:C,2,TRUE) | =VLOOKUP(A2,B:C,2,FALSE) |
| 조회값에 일반 공백 포함 | =VLOOKUP(A2,B:C,2,FALSE) | =VLOOKUP(TRIM(A2),B:C,2,FALSE) |
| 비표준 공백(줄바꿈 문자 등) | =VLOOKUP(TRIM(A2),B:C,2,FALSE) | =VLOOKUP(TRIM(SUBSTITUTE(A2,CHAR(160),\”\”)),B:C,2,FALSE) |
이렇게 고쳐도 여전히 #N/A가 뜬다면 조회값 자체가 원본 범위에 없거나, 숫자와 텍스트 형식이 섞여 있을 가능성이 크다. 아래 단계별 점검으로 넘어가면 된다.
VLOOKUP #N/A, 단계별로 원인을 찾는 방법
순서대로 하나씩 확인하면 스크린샷 없이도 바로 따라 할 수 있다.
- 수식 구조 확인: 수식 입력줄에서 마지막 인수가 FALSE 또는 0인지 본다. 숫자 0을 넣어도 FALSE와 동일하게 작동하므로 기존 수식 구조를 크게 바꾸지 않고도 고칠 수 있다. 리본 메뉴 수식 탭 > 함수 라이브러리 > 함수 삽입을 누르면 인수별 입력값을 하나씩 확인할 수 있다.
- 공백 여부 확인: 빈 열에 =TRIM(조회값 셀)=조회값 셀을 넣어 FALSE가 나오면 공백이 섞여 있다는 뜻이다. 결과가 TRUE인데도 #N/A가 계속 뜬다면 일반 공백이 아니라 CHAR(160) 같은 비표준 공백일 수 있으므로 =SUBSTITUTE(셀,CHAR(160),\”\”)로 한 번 더 걸러낸다. 홈 탭 > 편집 그룹 > 찾기 및 바꾸기에서 공백을 찾아 없음으로 바꿔도 된다.
- 데이터 형식 확인: 조회값 셀을 선택하고 홈 탭 > 셀 그룹 > 서식 > 셀 서식에서 범주를 본다. 셀 왼쪽 위에 녹색 삼각형 표시가 있다면 텍스트로 저장된 숫자라는 신호다. 한쪽은 숫자, 다른 쪽은 텍스트면 VALUE 또는 TEXT 함수로 형식을 맞춘다.
- 범위 참조 확인: 아래로 수식을 채울 때 범위가 같이 밀리지 않았는지 본다. 특히 범위 시작 행 번호가 수식을 채운 만큼 그대로 늘어나 있다면 거의 확실한 원인이다. 범위를 클릭하고 F4를 눌러 $B$2:$C$100처럼 절대참조로 고정한다.
- 수식 계산으로 단계 추적: 수식 탭 > 수식 분석 그룹 > 수식 계산을 누르면 VLOOKUP이 어느 단계에서 값을 못 찾는지 한 줄씩 확인할 수 있다. 조회값과 범위 첫 열의 실제 내용을 나란히 비교해보면 공백이나 형식 차이가 바로 눈에 보인다.
- 병합된 셀·숨겨진 열 확인: 원본 범위 첫 열에 병합된 셀이 섞여 있으면 VLOOKUP이 값을 제대로 읽지 못해 특정 행에서만 간헐적으로 오류가 난다. 홈 탭 > 맞춤 그룹 > 병합하고 가운데 맞춤으로 병합을 해제한 뒤 다시 시도한다.
참고로 TRIM이나 SUBSTITUTE를 배열 형태로 통째로 적용할 때, 2019 이전 구버전 엑셀에서는 수식을 입력한 뒤 Ctrl+Shift+Enter로 마무리해야 배열 수식으로 정상 인식된다.
Microsoft 365처럼 동적 배열을 지원하는 버전에서는 그냥 Enter만 눌러도 같은 결과가 나온다.
VLOOKUP #N/A, 안 될 때 원인별 해결 표
아래 표에서 자신의 증상과 가장 비슷한 줄을 찾아 해결법만 바로 적용하면 된다.
| 원인 | 증상 | 해결 방법 |
|---|---|---|
| 정확히 일치 옵션 누락(TRUE·생략) | 비슷한 값이 반환되거나 #N/A | 마지막 인수를 FALSE 또는 0으로 변경 |
| 조회값에 보이지 않는 공백 | 똑같아 보이는데 못 찾음 | TRIM으로 공백 제거한 도우미 열 사용 |
| 데이터 형식 다름(숫자 vs 텍스트) | 숫자처럼 보이는데 매칭 안 됨 | VALUE나 TEXT로 형식 통일 |
| 열 번호가 범위 밖 | #VALUE 또는 #REF와 혼동 | 열 번호를 범위 안으로 재설정 |
| 상대참조로 범위가 밀림 | 채우기 시 일부 행만 오류 | F4로 절대참조 고정 |
| 찾는 값이 원본에 없음 | 오타·데이터 누락 | 원본 확인 후 없으면 IFERROR로 표시 정리 |
| 범위에 병합된 셀 포함 | 특정 행에서만 간헐적으로 오류 발생 | 병합 해제 후 재시도 |
같이 알아두면 좋은 팁
VLOOKUP #N/A 오류를 자주 만난다면 아래 함수 차이도 함께 알아두면 다음부터 덜 헤매게 된다.
| 함수 | 역할 | 주의점 |
|---|---|---|
| IFERROR | 모든 오류를 같은 메시지로 숨김 | #NAME 같은 다른 오류도 같이 가려 원인 파악이 늦어질 수 있음 |
| IFNA | #N/A만 선택적으로 처리 | 다른 오류는 그대로 보여줘 디버깅에 유리 |
| XLOOKUP | 찾는 값이 없을 때 결과를 직접 지정 가능 | 구독형 Microsoft 365에서 주로 제공, 일부 영구 버전에는 없을 수 있어 자신의 버전에서 확인 필요 |
IFNA 사용법은 IFERROR와 거의 같다.
=IFNA(VLOOKUP(A2,B:C,2,FALSE),\”코드 확인 필요\”)처럼 쓰면 #N/A일 때만 지정한 문구가 뜨고, 수식 이름을 잘못 입력해 생기는 #NAME 오류는 그대로 드러나 바로 알아챌 수 있다.
XLOOKUP을 쓸 수 있는 환경이라면 네 번째 인수인 일치 모드를 2로 설정해 와일드카드 검색까지 가능하므로, 코드 뒷자리 몇 글자만 다른 경우를 찾을 때 특히 유용하다.
써보면 IFNA가 디버깅할 때 더 편하다. IFERROR로 감싸두면 수식 이름을 잘못 적은 #NAME 오류까지 같은 문구로 가려져서, 진짜 원인을 찾는 데 오히려 시간이 더 걸린 경험이 있다.
공백이 조회값이 아니라 검색 범위 쪽 셀에 있는 경우도 있다. 이때는 범위 자체를 TRIM으로 정리해야 하는데, 사용하는 엑셀 버전에 따라 입력 방식이 다를 수 있으니 자신의 버전에서 동적 배열이 지원되는지 먼저 확인하는 것이 안전하다.
엑셀뿐 아니라 다른 오피스 프로그램에서도 비슷하게 원인을 하나씩 좁혀가는 방식이 통한다. 한글 hwp 파일이 안 열릴 때의 해결법도 형식 불일치나 손상 여부를 순서대로 점검하는 구조라 참고할 만하다.
엑셀 파일 자체가 자꾸 느려지거나 멈춘다면 캐시 삭제 방법처럼 프로그램 임시파일 문제까지 같이 점검해보는 것도 방법이다.
정확한 함수별 지원 버전과 세부 동작은 Microsoft 공식 지원 문서에서 확인할 수 있다.
자주 묻는 질문
IFERROR로 감싸면 VLOOKUP #N/A 오류가 완전히 해결된 건가요?
아니다, 화면에 보이는 오류 표시만 가려지는 것이고 원인은 그대로 남아 있다. 데이터가 바뀌면 같은 자리에서 또 공백이나 형식 문제가 반복될 수 있으니 TRIM과 FALSE 옵션으로 원인부터 고치는 것이 먼저다. 특히 피벗 테이블이나 다른 셀이 그 결과값을 다시 참조하고 있다면, 가려진 오류가 0이나 빈 문자열로 집계에 섞여 들어가 전체 합계까지 틀어질 수 있으니 더 주의해야 한다.
VLOOKUP과 XLOOKUP 중 어느 쪽이 #N/A 관리에 더 편한가요?
XLOOKUP은 찾는 값이 없을 때 반환할 문구를 함수 안에서 바로 지정할 수 있어 IFERROR 없이도 깔끔하다. 다만 일부 영구 버전 엑셀에는 없을 수 있으므로 자신이 쓰는 버전에서 지원되는지 먼저 확인해야 한다. 또한 XLOOKUP은 조회 열이 결과 열보다 오른쪽에 있어도 순서를 바꾸지 않고 바로 찾을 수 있어, 열 순서 때문에 생기는 실수까지 함께 줄여준다.
조회값과 원본이 똑같아 보이는데 왜 VLOOKUP #N/A가 뜨나요?
가장 흔한 원인은 눈에 안 보이는 앞뒤 공백이나, 숫자인데 텍스트로 저장된 형식 차이다. 도우미 열에 TRIM을 적용하거나 셀 서식의 범주를 비교해보면 대부분 바로 드러난다. 셀 서식만 ‘일반’으로 바꿔서는 실제 저장된 값이 텍스트인 숫자가 바뀌지 않는 경우가 많으므로, 이럴 때는 빈 셀에 1을 곱하거나 VALUE 함수로 한 번 더 변환해주는 것이 확실하다.
VLOOKUP #N/A 오류는 결국 FALSE 옵션, 공백, 데이터 형식 세 가지만 순서대로 짚어보면 대부분 풀린다. 다음에는 XLOOKUP으로 넘어가는 실무 전환 방법도 정리해볼 예정이다. 여러분은 어떤 원인 때문에 VLOOKUP #N/A를 만났었나요?
출처: Editlab, https://editlab.luvpp.com
EDITLAB이 매체는 Editlab이 발행합니다분야별 전문 매체 8곳과 무료 데이터 도구를 함께 운영합니다.네트워크 보기 ›
글 Editlab 편집부 · 발행 전 자동 점검(중복·형식·규칙) · 정정 요청 · Google 검색에서 이 매체를 우선 출처로 등록
검색해도 내 집 사정과는 다를 때가 있습니다. 비슷한 상황을 지나온 분들에게 물어보실 수 있습니다. 글은 누구나 읽을 수 있고, 질문을 남기려면 카페 가입이 필요합니다.
EDITLAB 뉴스레터
오늘의 한 가지
평일 아침, 오늘 꼭 알아야 할 정보 하나. 신청 마감이 코앞인 지원금, 검색해도 안 나오는 실제 절차와 후기.
생년월일을 넣으면 매주 월요일 내 주간 운세도 같이 받을 수 있습니다.
개인정보처리방침 · 문의 editlab204@gmail.com · 발행 Editlab
