VLOOKUP #N/A 오류 뜬다면 원인별로 확인할 해결법 5가지

VLOOKUP #N/A 오류, 가장 빠른 해결책부터 말씀드립니다

VLOOKUP #N/A 오류는 대부분 찾는 값의 형식이나 공백 차이 때문에 생깁니다. 매일 엑셀로 데이터를 정리하는 실무자 기준으로 정리했습니다.

저도 얼마 전 거래처별 단가표에 VLOOKUP을 걸었다가 절반 넘는 행에서 #N/A만 뜬 적이 있습니다. 데이터를 눈으로 몇 번을 봐도 값은 똑같아 보였는데, 알고 보니 한쪽은 숫자로, 다른 한쪽은 텍스트로 입력된 코드번호였습니다.

정확히는 거래처 300곳 중 180곳, 그러니까 전체의 60%에 가까운 행에서 오류가 났습니다. 셀을 눌러봐도 화면에 보이는 숫자는 똑같아서 처음에는 수식 자체를 의심했는데, 셀 왼쪽 위에 작은 초록색 삼각형 표시가 있는 행만 골라보니 그 행들이 전부 텍스트 형식으로 저장된 코드였습니다. 이렇게 눈으로는 구분이 안 되는 형식 차이가 실무에서 가장 흔한 원인입니다.

이럴 때는 순서대로 짚으면 됩니다. 찾는 값과 참조표의 형식이 같은지, 앞뒤에 보이지 않는 공백이 있는지, 참조 범위가 절대참조로 고정됐는지, 찾는 값이 참조표의 맨 왼쪽 열에 있는지 네 가지만 확인하면 실무에서 만나는 오류의 대부분이 풀립니다. 경험상 형식 불일치가 전체 오류 원인의 절반 가까이를 차지하고, 그다음으로 공백 문제, 참조범위 밀림, 검색열 순서 문제가 뒤를 잇습니다.

원인 유형	체감 비중	알아채는 방법
텍스트·숫자 형식 불일치	약 45%	셀 왼쪽 위 초록 삼각형, 정렬 방향 차이
보이지 않는 공백(NBSP 포함)	약 25%	LEN 값이 눈으로 본 글자 수보다 큼
참조범위가 상대참조로 밀림	약 20%	일부 행만 오류, 나머지는 정상
검색열·옵션 설정 오류	약 10%	전체 행이 한꺼번에 오류

단계별로 따라 하는 VLOOKUP #N/A 오류 해결 방법

형식, 공백, 참조범위, 검색열 순서로 확인하면 되는데, 실제로 어디를 눌러야 하는지 메뉴 경로까지 정리했습니다.

1. 정확히 일치 옵션(FALSE)을 썼는지 확인했나요

수식의 네 번째 인수부터 확인하는 게 먼저입니다. 수식 입력줄을 눌러 =VLOOKUP(찾을값, 범위, 열번호, FALSE) 형태인지 보시기 바랍니다. 이 값을 생략하면 엑셀은 기본값인 TRUE(비슷한 값 찾기)로 인식해 데이터가 정렬돼 있지 않을 때 오류가 납니다.

TRUE나 생략 상태로 두면 엑셀은 참조 범위가 오름차순으로 정렬돼 있다고 가정하고 근사값을 찾습니다. 실제로는 정렬되지 않은 코드표에 이 옵션을 그대로 두면 존재하는 코드인데도 엉뚱한 값을 가져오거나 #N/A가 뜹니다. FALSE 또는 0을 넣으면 정확히 일치하는 값만 찾고, 없으면 확실하게 #N/A를 반환하므로 오히려 원인 파악이 쉬워집니다.

2. 공백과 보이지 않는 서식, 제거했나요

홈 > 편집 > 찾기 및 선택 > 바꾸기(단축키 Ctrl+H) 메뉴에서 공백 한 칸을 빈 칸으로 바꾸면 눈에 보이는 공백은 정리됩니다. 그래도 안 지워진다면 인터넷에서 복사한 데이터에 섞인 줄바꿈 없는 공백(NBSP)일 가능성이 높은데, 이때는 별도 열에 =TRIM(SUBSTITUTE(A1,CHAR(160),””)) 수식을 걸어 정리한 값으로 다시 VLOOKUP을 겁니다.

공백이 섞여 있는지 빠르게 확인하려면 같은 셀을 두고 =LEN(A1)과 =LEN(TRIM(A1)) 값을 나란히 비교해 보시기 바랍니다. 두 값이 다르면 눈에 안 보이는 공백이 섞여 있다는 뜻입니다. 특히 웹페이지나 이메일에서 복사해온 표는 일반 공백이 아닌 NBSP가 섞여 있는 경우가 많아 TRIM만으로는 안 지워지고, 위에서 설명한 SUBSTITUTE+CHAR(160) 조합을 함께 써야 완전히 제거됩니다.

3. 텍스트와 숫자 형식, 통일했나요

한쪽은 숫자, 한쪽은 텍스트로 입력된 코드가 가장 흔한 원인입니다. 데이터 > 데이터 도구 > 텍스트 나누기 메뉴를 선택하고 옵션을 바꾸지 않은 채 마침을 누르면 텍스트처럼 보이던 숫자가 진짜 숫자로 바뀝니다. 반대로 숫자를 텍스트로 맞추려면 =TEXT(A1,”0″) 함수를 씁니다.

숫자처럼 보이는 셀이 사실은 텍스트인지 확인하려면 셀을 클릭했을 때 값이 기본적으로 왼쪽 정렬(텍스트)인지 오른쪽 정렬(숫자)인지 보면 됩니다. =VALUE(A1) 함수로도 텍스트를 숫자로 바꿀 수 있지만, 원본에 콤마나 통화 기호가 섞여 있으면 오류가 나므로 이런 경우에는 텍스트 나누기 방법을 먼저 시도하는 게 안전합니다.

4. 참조 범위를 절대참조로 고정했나요

수식을 아래로 복사했을 때만 오류가 난다면 범위가 밀린 것입니다. 참조 범위를 드래그해 선택한 뒤 F4 키를 눌러 $A$1:$C$100처럼 절대참조로 바꾸면 복사해도 범위가 고정됩니다.

예를 들어 A2셀에 수식을 걸고 이를 50행까지 아래로 복사하면, 상대참조 상태에서는 범위가 한 칸씩 같이 밀려 51번째 행부터는 참조표 범위를 완전히 벗어나 버립니다. 절대참조로 고정한 뒤 다시 복사하면 모든 행이 처음 지정한 범위를 기준으로 값을 찾으므로 이런 밀림 현상이 사라집니다.

5. 찾는 값이 맨 왼쪽 열에 있나요

VLOOKUP은 참조 범위의 가장 왼쪽 열에서만 값을 찾습니다. 찾는 값이 오른쪽 열에 있다면 INDEX(MATCH()) 조합이나, 엑셀 2021과 Microsoft 365부터 쓸 수 있는 XLOOKUP 함수로 바꾸는 게 더 빠릅니다.

INDEX+MATCH로 바꿀 때는 =INDEX(C:C,MATCH(찾을값,A:A,0)) 형태로 쓰면 되고, XLOOKUP은 =XLOOKUP(찾을값,A:A,C:C)처럼 검색 열과 반환 열을 따로 지정하기 때문에 찾는 값이 어느 열에 있든 상관없이 동작합니다.

다섯 단계를 체크리스트로 정리하면 다음과 같습니다.

• 네 번째 인수에 FALSE 또는 0을 넣었는가
• LEN(A1)과 LEN(TRIM(A1)) 값이 같은가
• 양쪽 표의 형식(텍스트/숫자)이 같은가
• 범위를 F4로 절대참조 고정했는가
• 찾는 값이 참조표의 첫 열에 있는가

그래도 안 풀린다면 원인이 무엇일까요

위 다섯 단계를 다 확인했는데도 오류가 남아 있다면 아래 표에서 증상에 맞는 줄을 찾아보시기 바랍니다.

증상	진짜 원인	해결 방법
값이 똑같아 보이는데 #N/A	텍스트·숫자 형식 불일치	TEXT(), VALUE()로 형식 통일
복사해온 데이터만 오류	보이지 않는 공백(NBSP)	SUBSTITUTE+CHAR(160)로 제거
데이터 추가 후 갑자기 오류	참조 범위가 상대참조라 밀림	F4로 절대참조 고정
찾는 값이 오른쪽 열에 있음	VLOOKUP은 첫 열만 검색	INDEX+MATCH 또는 XLOOKUP
전체 행이 다 #N/A	마지막 인수 생략(기본값 TRUE)	마지막 인수에 FALSE 또는 0 입력
값이 없는 게 정상인데 오류 표시가 싫음	오류가 아니라 정상적인 미존재	IFERROR로 대체 문구 표시

표에서 가장 눈여겨볼 부분은 ‘전체 행이 다 #N/A’와 ‘값이 없는 게 정상인데 오류 표시가 싫음’을 구분하는 것입니다. 앞의 경우는 수식 설정 자체를 고쳐야 하는 진짜 오류지만, 뒤의 경우는 데이터가 원래 없는 정상적인 상황이므로 IFERROR로 감싸는 것만으로 충분합니다. 이 둘을 헷갈려서 정상적인 빈 값까지 억지로 채우려다 데이터가 뒤틀리는 경우도 종종 봤습니다.

진단 순서를 한눈에 정리하면 어떻게 될까요

아래 흐름대로 위에서부터 하나씩 지워나가면 원인을 빠르게 좁힐 수 있습니다. 실제로 이 순서대로 확인하면 대부분 세 번째 단계 안에서 원인이 드러나고, 다섯 단계를 전부 거쳐도 풀리지 않는 경우는 드뭅니다.

VLOOKUP과 함께 알아두면 좋은 실무 팁

VLOOKUP #N/A 오류를 안 보이게 하는 것과 원인을 고치는 것은 다른 얘기입니다. =IFERROR(VLOOKUP(찾을값,범위,열번호,FALSE),”값없음”)로 감싸면 화면은 깔끔해지지만, 형식이나 공백 문제는 그대로 남아 다른 수식에서 또 오류를 일으킬 수 있습니다. 예를 들어 이 값을 다시 SUM이나 다른 VLOOKUP의 참조값으로 쓰면 “값없음”이라는 텍스트 때문에 새로운 오류가 발생하기도 합니다.

가능하면 XLOOKUP으로 바꾸는 것도 방법입니다. 엑셀 2021과 Microsoft 365 이상에서 지원되는 함수로, =XLOOKUP(찾을값,A:A,C:C,”코드 확인 필요”)처럼 네 번째 인수에 바로 대체값을 지정할 수 있어 IFERROR를 따로 씌우지 않아도 되고 찾는 값이 참조표 오른쪽에 있어도 동작합니다. 다만 엑셀 2019 이하 버전에서는 지원되지 않으므로 회사 컴퓨터의 버전을 먼저 확인하는 게 좋습니다.

함수	검색 방향	대체값 지정	지원 버전
VLOOKUP	왼쪽 열만 검색 가능	IFERROR로 별도로 감싸야 함	모든 버전
INDEX+MATCH	양방향 검색 가능	IFERROR로 별도로 감싸야 함	모든 버전
XLOOKUP	양방향 검색 가능	네 번째 인수에 바로 지정	엑셀 2021, Microsoft 365

대량 데이터를 다룰 때는 VLOOKUP을 걸기 전에 Ctrl+F로 값 하나를 검색해 표시 형식이나 공백을 눈으로 먼저 확인하는 습관을 들이면 나중에 오류를 되짚는 시간을 크게 줄일 수 있습니다. 예를 들어 1,000행짜리 표라면 전체에 수식을 걸기 전에 값 한두 개만 먼저 검색해 형식이 맞는지 확인하는 데 1분도 걸리지 않지만, 이 습관 하나로 나중에 오류 원인을 처음부터 되짚는 수십 분을 아낄 수 있습니다. 스프레드시트라는 도구 자체의 기본 개념은 위키백과 Microsoft Excel 문서에도 정리돼 있으니 함수 체계가 낯설다면 한 번 훑어볼 만합니다.

엑셀뿐 아니라 사무 문서 전반에서 이런 서식 문제는 자주 겹칩니다. 한글에서 표가 안 만들어지거나 깨질 때는 한글 표 만들기 단축키와 메뉴 경로, 표 안 될 때 해결법을 참고하면 되고, 프로그램 실행 자체가 안 될 때는 exe 파일 실행 안됨 문제 해결 순서를 먼저 확인해 보는 것도 도움이 됩니다.

자주 묻는 질문

IFERROR로 오류만 가리면 문제가 해결된 건가요

아닙니다. IFERROR는 화면에 오류 대신 다른 문구를 보여줄 뿐이고, 형식 불일치나 공백 같은 진짜 원인은 그대로 남아 다른 수식에서 다시 문제가 될 수 있습니다.

XLOOKUP을 쓰면 #N/A가 아예 안 뜨나요

XLOOKUP도 찾는 값이 없으면 똑같이 #N/A를 반환합니다. 다만 네 번째 인수에 대체 문구를 바로 지정할 수 있어 IFERROR 없이도 깔끔하게 처리할 수 있다는 차이가 있습니다.

구글 시트에서도 같은 방법으로 고칠 수 있나요

원인은 엑셀과 같지만 메뉴 이름이 다릅니다. 공백 제거는 데이터 메뉴 안의 정리 기능을 쓰는 식으로, 같은 원리를 구글 시트 자체 메뉴에서 찾아 적용하면 됩니다.

정리하면 VLOOKUP #N/A 오류는 형식, 공백, 참조범위, 검색열 이 네 가지만 순서대로 확인해도 대부분 풀립니다. 다음에는 XLOOKUP과 INDEX+MATCH를 언제 나눠 써야 하는지도 다뤄보겠습니다. 여러분은 VLOOKUP 오류를 만났을 때 주로 어떤 원인이었나요, 댓글로 경험을 나눠주시면 다음 글에 참고하겠습니다.

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

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