VLOOKUP #N/A 오류 원인 4가지, 설정 확인하는 순서

거래처 코드로 매칭표를 만들다가 VLOOKUP 셀마다 #N/A가 줄줄이 뜨는 걸 본 적이 있다면, 이 글이 그 상황을 바로 겨냥한 글이다.

예를 들어 거래처 200곳의 코드로 단가표를 대조했는데 그중 30곳 넘는 셀에서 한꺼번에 #N/A가 뜨는 경우가 실무에서 흔하다.

엑셀로 매출표나 명단을 대조할 때 가장 자주 만나는 이 오류는 수식이 틀려서가 아니라 데이터 쪽 문제인 경우가 대부분이다.

결론부터 말하면 네 번째 인수를 FALSE로 바꾸고 조회값에 숨은 공백이 있는지부터 확인하면 절반 이상은 바로 풀린다.

실제로 거래처 코드 목록처럼 외부 시스템에서 내려받은 데이터를 다룰 때마다 이 두 가지만 체크해도 오류 건수가 눈에 띄게 줄었고, 나머지는 데이터 형식 차이나 열 위치 문제로 좁혀지는 경우가 많았다.

VLOOKUP #N/A 오류, 가장 빠르게 고치는 방법은 무엇일까요

네 번째 인수를 FALSE로 설정하고 조회값의 공백·서식을 맞추는 것이 가장 빠른 해결책이다.

VLOOKUP은 =VLOOKUP(찾을값, 범위, 열번호, 정확히일치여부) 구조인데, 마지막 인수를 비워두거나 TRUE로 두면 비슷한 값까지 끌어와 오류나 엉뚱한 결과를 낸다.

예를 들어 =VLOOKUP(A2,B2:D100,3,TRUE)처럼 네 번째 인수가 TRUE거나 생략돼 있으면, 범위가 정렬돼 있지 않을 때 전혀 다른 행의 값을 가져오고도 오류조차 띄우지 않는 경우가 있어 더 위험하다.

Microsoft 공식 지원 문서도 #N/A는 수식이 찾으려는 값을 원본 데이터에서 찾지 못했을 때 나타난다고 설명한다.

그래서 가장 먼저 할 일은 수식 맨 끝에 FALSE 또는 0을 넣어 정확히 일치 검색으로 바꾸는 것이다.

=VLOOKUP(A2,B2:D100,3,FALSE) 형태로 고치면 적어도 비슷한 값을 잘못 가져오는 위험은 사라지고, 정말로 값이 없을 때만 오류가 뜨게 된다. 그다음 조회값 셀에 눈에 안 보이는 공백이 있는지 확인한다.

VLOOKUP #N/A 오류 해결 단계별 방법

아래 순서를 위에서부터 그대로 따라가면 대부분의 #N/A 원인을 찾아낼 수 있다.

1. 수식 확인 — 수식 입력줄에서 네 번째 인수가 FALSE 또는 0인지 확인한다. 비어 있으면 TRUE로 인식돼 근사값을 찾는다. 수식이 여러 셀에 복사돼 있다면 전체를 드래그해서 한 번에 확인하는 것이 빠르다.
2. 조회값 존재 확인 — Ctrl+F로 찾을 값을 범위에서 직접 검색해 실제로 있는지 본다. 자동 완성이나 복사-붙여넣기 과정에서 값 자체가 통째로 빠진 경우도 적지 않다.
3. 공백·특수문자 제거 — 빈 열에 =TRIM(CLEAN(A2))를 넣고, 그 결과를 값으로 복사해 조회값 대신 사용한다. 특히 웹페이지나 ERP 시스템에서 복사한 데이터는 일반 스페이스가 아닌 ‘줄바꿈 없는 공백(CHAR(160))’이 섞여 있어 TRIM만으로 안 지워지는 경우가 많으니 이 단계를 건너뛰지 않는 게 중요하다.
4. 데이터 형식 통일 — 홈 > 표시 형식에서 조회값과 범위 첫 열이 둘 다 텍스트인지, 숫자인지 맞춘다. 숫자가 텍스트로 저장돼 있으면 셀 왼쪽 위에 녹색 삼각형이 뜨는데, 이때는 데이터 > 텍스트 나누기 > 마침을 눌러 한꺼번에 숫자 형식으로 바꿀 수 있다. 반대로 숫자를 텍스트로 바꿔야 한다면 셀 서식을 텍스트로 지정한 뒤 값을 다시 입력하거나, 빈 열에 =TEXT(A2,"0")을 적용한다.
5. 열 위치 확인 — VLOOKUP은 범위의 첫 번째 열에서만 찾는다. 찾을 값이 있는 열이 범위 왼쪽 끝이 아니면 범위를 다시 잡거나 열 순서를 바꾼다. 원본 표의 열 순서를 바꾸기 어렵다면 INDEX와 MATCH를 조합하거나 XLOOKUP으로 전환하는 쪽이 더 안전하다.

위 다섯 단계 중 셋째와 넷째 단계만 먼저 점검해도 실무에서 발생하는 오류의 상당수는 해결된다. 그래도 특정 행에서만 오류가 반복된다면 해당 행의 원본 데이터를 셀 단위로 다시 입력해보는 것도 빠른 확인 방법이다.

그래도 안 될 때는 어떤 원인부터 확인해야 할까요

단계를 따라도 안 풀리면 아래 표에서 겉보기엔 비슷해 보이는 원인을 다시 짚어봐야 한다.

특히 같은 파일 안에서도 특정 열이나 특정 담당자가 입력한 구간에서만 오류가 몰려 있다면, 그 구간만 다른 방식으로 입력됐을 가능성이 크다.

증상	진짜 원인	해결 방법
값이 분명히 있는데도 #N/A	조회값 앞뒤에 보이지 않는 공백	TRIM(CLEAN())으로 정리한 도우미 열 사용
숫자로 찾는데 계속 실패	한쪽은 숫자, 한쪽은 텍스트로 저장된 숫자	표시 형식 통일 또는 데이터 > 텍스트 나누기로 숫자 변환
엉뚱한 값이 나오거나 #N/A	네 번째 인수가 비어 있거나 TRUE	인수를 FALSE로 명시
분명 같은 열에 있는데 못 찾음	찾을 값이 범위의 첫 번째 열이 아님	범위를 다시 지정하거나 XLOOKUP으로 전환
병합 범위에서만 오류	셀 병합으로 범위 구조가 깨짐	병합 해제 후 다시 수식 작성
다른 시트를 참조할 때만 오류	참조 범위에 필터·숨김 행이 적용돼 실제 데이터와 안 맞음	필터를 해제하거나 표 전체 범위를 다시 지정

실무에서는 이 중에서도 공백과 숫자·텍스트 혼용 두 가지가 체감상 가장 흔했다.

외부 시스템에서 다운로드한 명단을 그대로 붙여넣었을 때 특히 자주 보였고, 거래처 코드나 전화번호처럼 숫자로만 이뤄진 값일수록 혼용 문제가 눈으로는 잘 드러나지 않아 발견이 늦어지는 경향이 있다.

VLOOKUP 쓸 때 같이 알아두면 좋은 팁

오류를 숨기는 것과 원인을 없애는 것은 다르다는 점을 먼저 구분해야 한다.

IFERROR로 수식을 감싸면 화면에서 #N/A가 사라지지만, 원본 데이터의 문제는 그대로 남는다.

예를 들어 =IFERROR(VLOOKUP(A2,B:D,3,FALSE),"")처럼 쓰면 결과 칸이 빈 칸으로만 보여서, 나중에 ‘값이 원래 없는 것’과 ‘수식이 실패한 것’을 구분할 수 없게 된다.

대신 #N/A만 따로 처리하는 IFNA 함수를 쓰면 다른 오류(#REF!, #VALUE! 등)는 그대로 드러나서 진짜 수식 오류를 놓치지 않을 수 있다.

=IFNA(VLOOKUP(A2,B:D,3,FALSE),"코드없음") 식으로 써두면, 조회값이 없는 경우만 ‘코드없음’으로 표시되고 수식 자체가 깨졌을 때는 원래 오류가 그대로 보여서 점검하기 쉽다.

구분	VLOOKUP	XLOOKUP
검색 방향	항상 범위의 첫 번째 열만	왼쪽·오른쪽 어느 열이든 가능
못 찾았을 때	#N/A 반환	대체 텍스트 직접 지정 가능
사용 가능 버전	대부분의 엑셀 버전	Microsoft 365, 최신 Excel 버전(제공처에 따라 상이)

• 원본 데이터를 받으면 바로 쓰지 말고 TRIM·CLEAN으로 한 번 정리한 사본을 만들어 둔다.
• 조회 범위는 되도록 표(Table) 기능으로 지정해 행이 추가돼도 범위가 자동으로 늘어나게 한다.
• 같은 파일을 여러 사람이 입력한다면 데이터 유효성 검사로 입력 형식을 미리 제한해 둔다.

구독형 Microsoft 365를 쓰고 있다면 XLOOKUP으로 바꾸는 것만으로 열 위치 제약 문제가 사라진다.

다만 오래된 버전의 엑셀에서는 XLOOKUP 자체가 없을 수 있으니, 정확한 지원 버전은 사용 중인 오피스 제품의 업데이트 상태에 따라 다르므로 메뉴에서 함수 자동완성으로 직접 확인하는 것이 가장 정확하다.

참고로 비밀번호 인증 실패처럼 겉보기엔 같아 보이는 값이 실제로는 다르게 인식되는 문제는 엑셀 수식 오류와도 원인 구조가 비슷하다. 눈에 안 보이는 차이를 먼저 의심하는 습관이 핵심이다.

VLOOKUP #N/A 오류 자주 묻는 질문

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

아니다, 화면에 뜨는 오류 표시만 숨길 뿐 원인이 되는 데이터 문제는 그대로 남는다. 보고서 제출 전에 임시로 깔끔하게 보이려는 목적이 아니라면 원인부터 찾는 게 먼저다. 특히 빈 칸으로 처리해 버리면 나중에 ‘정말 값이 없는 것’과 ‘수식이 실패한 것’을 구분하기 어려워져서 집계가 틀어질 수 있다.

공백 제거는 했는데도 계속 #N/A가 떠요

일반 TRIM으로 안 지워지는 특수 공백(줄바꿈 없는 공백 등)이 섞여 있을 수 있다. =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))) 형태로 한 단계 더 처리해보면 해결되는 경우가 많다. 외부 웹사이트나 PDF에서 복사한 데이터일수록 이런 특수 공백이 포함될 확률이 높으므로, 애초에 복사 단계에서부터 한 번 더 정리하는 습관을 들이는 것이 좋다.

VLOOKUP 대신 XLOOKUP을 바로 써도 되나요

Microsoft 365나 최신 Excel 버전을 쓰고 있다면 가능하다. 열 위치 제약이 없고 못 찾았을 때 표시할 값도 직접 지정할 수 있어 #N/A 관리가 더 쉬워진다. 다만 공유 파일을 구버전 엑셀 사용자와 주고받는다면 호환성을 먼저 확인해야 한다. 같은 파일을 여러 버전의 오피스에서 열어야 하는 상황이라면, 당장 전체를 바꾸기보다 새로 만드는 수식에만 XLOOKUP을 적용하는 식으로 점진적으로 전환하는 것도 현실적인 방법이다.

정리하면 VLOOKUP #N/A 오류는 수식 자체보다 데이터의 숨은 차이 때문에 생기는 경우가 훨씬 많다. 인수를 FALSE로 맞추고 공백·형식·열 위치 네 가지만 순서대로 짚어보면 대부분 해결된다.

여러분은 어떤 원인으로 #N/A를 가장 자주 겪으셨나요, 댓글로 상황을 남겨주시면 다음 글에서 더 구체적인 사례로 다뤄보겠다.

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

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

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