엑셀 VLOOKUP #N/A 오류 해결방법, 어디서부터 고쳐야 할까?

엑셀로 거래처 코드나 직원 번호를 불러오다가 VLOOKUP이 갑자기 #N/A를 뱉어내면 당황스럽다. 엑셀 2021, 엑셀 365, 엑셀 온라인에서 똑같이 실무 데이터를 다루다가 자주 막히는 지점을 기준으로 정리했다.

끝까지 읽으면 네 번째 인수 설정부터 공백, 서식 문제까지 순서대로 점검해 몇 분 안에 오류를 없앨 수 있다.

예를 들어 거래처 500곳의 코드를 담은 표에서 VLOOKUP으로 담당자 이름을 불러오다가 그중 서른에서 마흔 개 셀에서만 오류가 뜨는 경우가 흔하다.

범위를 지정한 모든 셀에서 똑같이 오류가 난다면 네 번째 인수나 참조 범위 자체를 먼저 점검하고, 일부 셀에서만 오류가 난다면 그 셀의 데이터 형식이나 공백 문제일 가능성이 크다고 보면 된다.

전체가 멈추는지 일부만 걸리는지를 먼저 구분하는 것만으로도 점검 순서를 절반은 줄일 수 있다.

VLOOKUP #N/A, 가장 빨리 고치는 방법은 무엇일까

대부분은 VLOOKUP의 네 번째 인수를 FALSE로 바꾸는 것만으로 해결된다.

VLOOKUP 수식은 =VLOOKUP(찾을값, 범위, 열번호, 일치옵션) 구조인데, 마지막 일치옵션을 TRUE로 두거나 아예 생략하면 정확히 같은 값이 없어도 비슷한 값을 억지로 찾다가 #N/A가 난다.

실제로 사번이나 거래처 코드처럼 딱 맞는 값을 찾아야 하는 데이터에서 이 부분을 빼먹고 쓰는 경우를 자주 본다.

그래서 가장 먼저 수식을 =VLOOKUP(A2, B:C, 2, FALSE) 처럼 네 번째 자리에 FALSE 또는 0을 넣어 정확히 일치하는 값만 찾도록 고정해야 한다. 이 한 줄만 바꿔도 전체 오류 중 상당수가 바로 사라진다.

예를 들어 사번 101, 102, 105만 있는 표에서 103번 사번을 찾는다고 하면, 네 번째 인수가 TRUE이거나 비어 있을 때는 엑셀이 비슷하게 가장 가까운 값인 102번 데이터를 억지로 반환해버린다.

반면 FALSE로 지정하면 정확히 103번이 없다는 것을 그대로 #N/A로 알려준다. 즉 TRUE 옵션은 오류를 숨기는 것이 아니라 틀린 값을 조용히 돌려주는, 더 위험한 상황을 만들 수 있다는 점도 함께 기억해두면 좋다.

단계별로 따라하면 바로 해결되는 방법

수식 입력줄에서 네 번째 인수부터 공백, 범위까지 순서대로 고치면 된다.

1. 1단계. 오류가 난 셀을 클릭하고 수식 입력줄에서 VLOOKUP 괄호 안 마지막 값을 확인한다. TRUE이거나 비어 있으면 FALSE로 바꾼다.
2. 2단계. 찾을값 셀을 선택한 뒤 리본의 홈 > 편집 > 지우기 > 서식 지우기를 눌러 숫자가 텍스트 서식으로 저장돼 있지 않은지 확인한다. 셀 좌측 상단에 작은 녹색 삼각형이 있으면 숫자가 텍스트로 저장된 신호다. 반대로 원본 데이터 쪽 숫자가 텍스트로, 찾을값 쪽이 진짜 숫자로 저장돼 있는 경우도 똑같이 오류를 만드니 두 쪽 모두 확인해야 한다.
3. 3단계. 빈 셀에 =TRIM(A2) 를 넣어 보조 열을 만들고, 그 결과를 VLOOKUP의 찾을값으로 바꿔 앞뒤 공백을 제거한다. 눈에는 안 보이지만 복사해온 데이터 끝에 공백이나 줄바꿈 문자가 끼어 있는 경우가 의외로 많다. 웹페이지나 다른 시스템에서 복사한 데이터일수록 이런 숨은 공백이 끼어 있을 확률이 높다.
4. 4단계. 범위를 지정한 열 번호가 실제 데이터 범위 안에 있는지, 찾는 값이 조회 범위의 첫 번째 열에 있는지 다시 확인한다. VLOOKUP은 항상 범위의 첫 번째 열에서만 값을 찾는다. 열 번호를 손으로 세다가 하나씩 밀리는 실수도 자주 나오니, 머리글 행부터 다시 세어보는 것이 안전하다.
5. 5단계. 여기까지 했는데도 안 되면 원본 데이터 목록을 Ctrl+F로 직접 검색해, 찾는 값이 데이터 자체에 아예 없는지 확인한다.
6. 6단계. 수식을 여러 셀에 복사해서 썼다면 범위 참조에 절대참조 기호(F4)를 걸어뒀는지도 살펴본다. 상대참조 상태로 아래로 복사하면 참조 범위의 시작 행이 한 칸씩 밀려 내려가면서 정상적인 데이터 구간을 벗어나 조회하게 되고, 그 결과 오류가 발생한다.

개인적으로 숫자로만 된 거래처 코드를 조회할 때 이 문제를 가장 자주 겪었다. 원본 표에서는 코드가 숫자로, 조회하는 표에서는 텍스트로 저장돼 있어서 서식만 맞추니 바로 해결된 경우가 많았다.

특히 ERP나 회계 프로그램에서 내려받은 데이터를 엑셀로 옮길 때 숫자 앞에 작은따옴표가 붙어 텍스트로 바뀌는 일이 흔하므로, 외부에서 가져온 자료를 다룰 때는 이 부분을 먼저 의심해보는 습관을 들이는 것이 좋다.

그래도 안 될 때, 원인은 무엇일까

5단계를 다 거쳤는데도 안 되면 데이터 형식이나 참조 범위 자체가 문제인 경우가 많다. 아래 표에서 자신의 증상과 가장 비슷한 항목을 찾아 해당 해결법부터 적용해보자.

증상	원인	해결법
FALSE로 바꿔도 #N/A	찾을값과 원본 데이터의 숫자/텍스트 형식이 다름	=VALUE() 또는 =TEXT()로 형식을 통일
분명히 있는 값인데 안 찾아짐	복사해온 데이터 끝에 공백이나 줄바꿈 포함	TRIM, CLEAN 함수로 보조열 생성
표 일부만 조회 안 됨	범위를 절대참조($)로 고정하지 않아 수식 복사 시 범위가 밀림	범위를 $B$2:$C$100처럼 절대참조로 고정
다른 시트 참조 시 오류	원본 시트명이 바뀌었거나 범위가 삭제됨	시트명과 참조 범위를 다시 지정
숫자처럼 보이는데도 오류	셀 서식이 ‘회계’ 등 특수 서식으로 숫자가 변형 저장됨	서식 지우기 후 숫자로 재입력
병합된 셀이 포함된 범위	병합 셀 때문에 행과 열의 기준이 어긋나 참조 범위가 틀어짐	병합을 모두 해제하고 각 셀에 값을 다시 채워 넣기

표에 있는 원인 중 두세 개가 동시에 겹쳐 있는 경우도 적지 않다. 예를 들어 서식도 다르고 공백도 섞여 있는 데이터라면, 위 표의 해결법을 하나씩 순서대로 적용해보면서 바뀌는지 확인하는 편이 원인을 빨리 좁히는 방법이다.

헷갈리기 쉬운 범위 지정 실수

VLOOKUP은 조회 범위의 맨 왼쪽 열만 기준으로 값을 찾는다는 점을 놓치기 쉽다.

찾고 싶은 값이 범위의 두 번째나 세 번째 열에 있으면 VLOOKUP 구조상 절대 찾을 수 없다.

예를 들어 이름이 B열에, 사번이 A열에 있는 표에서 이름을 기준으로 사번을 찾으려 하면, 범위를 B열부터 지정하는 순간 VLOOKUP은 A열을 더 이상 볼 수 없다.

이럴 때는 열 순서를 바꾸거나, 뒤에서 설명할 XLOOKUP이나 INDEX+MATCH 조합을 쓰는 것이 맞는 방법이다.

같이 알아두면 좋은 엑셀 팁

오류를 숨기고 싶다면 IFERROR나 IFNA로 감싸면 되고, 더 유연한 수식을 쓰고 싶다면 XLOOKUP이 대안이다.

IFERROR는 모든 종류의 오류를 잡아 대체값을 보여준다. 예를 들어 =IFERROR(VLOOKUP(A2,B:C,2,FALSE),”없음”) 로 쓰면 #N/A뿐 아니라 다른 오류도 모두 “없음”으로 바뀐다.

반면 IFNA는 #N/A 오류만 골라서 처리하므로, 범위 자체가 잘못됐을 때 생기는 다른 오류는 그대로 드러나게 하고 싶을 때 더 정확하다.

보고서나 대시보드처럼 빈 칸이 어색해 보이는 양식에서는 “없음” 대신 공백이나 하이픈으로 대체값을 지정하는 경우도 많다.

XLOOKUP으로 바꾸면 더 편할까

엑셀 365, 엑셀 2021 이상에서는 XLOOKUP이 VLOOKUP보다 쓰기 편하다.

XLOOKUP은 =XLOOKUP(찾을값, 찾을범위, 반환범위, 못찾았을때값) 구조라, 네 번째 인수에 바로 대체값을 넣을 수 있어 IFERROR를 따로 씌울 필요가 없다.

다만 엑셀 2016, 2019 영구 라이선스 버전에는 XLOOKUP이 없으므로, 회사 PC 버전을 먼저 확인하는 것이 순서다.

항목	VLOOKUP	XLOOKUP
찾을 열 위치	항상 범위의 첫 번째 열	범위 안 어디든 지정 가능
기본 일치 방식	네 번째 인수를 생략하면 근사값으로 처리돼 위험	기본값이 정확히 일치라 더 안전
오류 대체값 지정	별도로 IFERROR를 씌워야 함	네 번째 인수에 바로 지정 가능
지원 버전	모든 버전에서 사용 가능	엑셀 2021, Microsoft 365만 지원

회사나 거래처와 파일을 주고받는 환경이라면, 받는 쪽 엑셀 버전에 XLOOKUP이 없을 수도 있다는 점을 감안해 VLOOKUP과 IFERROR 조합을 함께 남겨두는 것도 현실적인 선택이다.

도움이 될 만한 다른 글로 계정 보안이 걸리는 상황별 점검 순서처럼, 업무 파일을 다룰 때 함께 챙겨두면 좋은 기본 점검 습관도 참고할 만하다.

자주 묻는 질문

VLOOKUP과 XLOOKUP 중 뭘 써야 하나요

엑셀 2021 이상이나 Microsoft 365를 쓴다면 XLOOKUP이 더 편하다. 못 찾았을 때 대체값을 바로 지정할 수 있어 수식이 짧아지고, 왼쪽 열 기준이라는 제약도 없다.

IFERROR로 감싸면 오류가 완전히 해결되나요

아니다. IFERROR는 #N/A를 다른 값으로 보이게 가려줄 뿐, 데이터 자체의 불일치나 공백 문제는 그대로 남아 있다. 보고서용으로 보이지 않게 할 때만 쓰고, 원인은 따로 확인하는 것이 안전하다.

분명 있는 값인데 계속 #N/A가 뜨면 뭘 먼저 봐야 하나요

먼저 찾을값 셀과 원본 데이터 열의 형식(숫자/텍스트)이 같은지 확인한다. 그다음 TRIM으로 공백을 제거해보고, 그래도 안 되면 Ctrl+F로 원본 데이터에 그 값이 실제로 있는지 직접 검색해본다.

엑셀 VLOOKUP #N/A 오류는 대부분 네 번째 인수, 서식, 공백, 범위 네 가지 중 하나에서 생긴다. 순서대로 짚어가면 복잡한 수식을 새로 짤 필요 없이 몇 분 안에 해결된다.

특히 데이터 양이 많아질수록 서식 불일치나 공백 문제는 눈으로만 봐서는 찾기 어려우므로, 의심되는 셀에 TRIM이나 LEN 함수로 보조 열을 하나 만들어두면 다음에 비슷한 문제가 생겼을 때도 훨씬 빠르게 원인을 좁힐 수 있다.

자세한 함수 설명은 Microsoft 공식 지원 문서에서도 확인할 수 있다. 여러분은 어떤 부분에서 #N/A가 가장 자주 났나요, 댓글로 각자의 경험을 나눠보면 좋겠다.

출처: Editlab, https://editlab.luvpp.com

EDITLAB이 매체는 Editlab이 발행합니다분야별 전문 매체 8곳과 무료 데이터 도구를 함께 운영합니다.네트워크 보기 ›

글 Editlab 편집부 · 발행 전 자동 점검(중복·형식·규칙) · 정정 요청 · Google 검색에서 이 매체를 우선 출처로 등록