VLOOKUP 오류 #N/A 떴다면 가장 먼저 확인할 원인 5가지

VLOOKUP 오류, 결론부터 말하면 이 순서로 확인하면 풀립니다

VLOOKUP 오류는 대부분 찾는 값과 참조 표 첫 열의 값이 겉보기엔 같아도 실제로는 다르기 때문에 생깁니다.

저도 정산 시트를 만들다가 분명히 눈으로는 똑같은 숫자인데 계속 #N/A가 떠서 30분을 헤맨 적이 있는데, 알고 보니 한쪽은 숫자, 한쪽은 텍스트로 저장된 게 원인이었습니다.

예를 들어 거래처 코드가 담긴 셀이 왼쪽 표에서는 ‘00123’처럼 앞에 0이 붙은 텍스트로 저장돼 있고, 오른쪽 참조 표에서는 셀 서식이 숫자로 바뀌면서 123으로 저장돼 있다면, 화면에 보이는 자릿수만 다를 뿐 값 자체가 달라 VLOOKUP은 이 둘을 완전히 다른 값으로 취급합니다. 이런 눈에 안 보이는 차이가 실무에서 겪는 #N/A 오류의 상당수를 차지합니다.

순서는 간단합니다. ①값 형식 확인 → ②숫자·텍스트 통일 → ③참조 범위 고정 → ④공백 제거 → ⑤정확히 일치(FALSE) 확인 순으로 5가지만 짚으면 대부분 바로 해결됩니다. 아래 단계별 설명과 표를 순서대로 따라가면 원인을 특정하는 데 걸리는 시간을 크게 줄일 수 있습니다.

VLOOKUP 오류 해결 단계별 방법

화면 캡처 없이도 메뉴 경로만 따라가면 그대로 고칠 수 있도록 순서대로 정리했습니다. 순서를 건너뛰지 말고 위에서부터 하나씩 확인하는 편이 원인을 빠르게 좁히는 데 유리합니다.

1단계, 찾는 값과 참조 표 서식이 같은지 확인

먼저 홈 > 표시 형식 드롭다운에서 찾는 값이 있는 셀과 참조 표 첫 열의 셀이 같은 형식인지 봅니다.

하나는 ‘일반’인데 다른 하나는 ‘텍스트’로 표시되면 눈에는 똑같은 100이어도 VLOOKUP은 서로 다른 값으로 인식해 #N/A를 반환합니다.

셀을 선택했을 때 값이 셀 왼쪽에 붙어 있으면 대부분 텍스트, 오른쪽에 붙어 있으면 숫자로 저장된 것이니 별도 프로그램 없이도 눈으로 1차 판별이 가능합니다. 회계 프로그램이나 웹사이트에서 내려받은 표는 코드값이 텍스트로 저장돼 있는 경우가 특히 많습니다.

2단계, 텍스트로 저장된 숫자를 진짜 숫자로 변환

데이터 > 데이터 도구 > 텍스트 나누기를 실행한 뒤 옵션을 바꾸지 말고 그대로 ‘마침’만 누르면 텍스트였던 숫자가 진짜 숫자로 바뀝니다.

빈 셀에 1을 입력해 복사한 다음 대상 범위를 선택하고 붙여넣기 > 선택하여 붙여넣기 > 곱하기를 눌러도 같은 효과를 봅니다.

둘 중 어느 방법을 쓰든 변환 전에는 반드시 원본 열을 옆에 하나 더 복사해 두는 편이 안전합니다. 데이터 양이 많을 때는 =VALUE(셀주소) 함수를 새 열에 걸어 숫자로 바꾼 다음, 결과값만 복사해 ‘값 붙여넣기’로 원래 자리에 덮어써도 됩니다.

3단계, 참조 범위를 절대참조로 고정

수식 입력줄에서 참조 범위를 마우스로 선택한 뒤 F4 키를 눌러 $A$2:$D$100처럼 고정합니다.

고정하지 않으면 수식을 아래로 채울 때 범위가 같이 밀려 내려가면서 엉뚱한 결과나 #N/A가 나옵니다.

F4를 반복해서 누르면 $A$2:$D$100 → A$2:D$100 → $A2:$D100 → A2:D100 순서로 참조 형태가 바뀌는데, 아래로도 옆으로도 채우기를 할 계획이라면 행·열 모두 고정한 $A$2:$D$100 형태를 쓰는 것이 가장 안전합니다. 참조 표 범위가 자주 늘어난다면 Ctrl+T로 표 서식을 적용해 구조적 참조(표1[#전체])로 바꿔두면 F4로 매번 고정할 필요 없이 행이 추가돼도 범위가 자동으로 늘어납니다.

4단계, 보이지 않는 공백과 특수문자 제거

새 셀에 =TRIM(CLEAN(찾는값셀))을 입력해 결과를 값으로 붙여넣은 뒤 VLOOKUP을 다시 걸어봅니다.

웹페이지나 다른 프로그램에서 복사해 붙여넣은 데이터는 눈에 안 보이는 공백이 섞여 있는 경우가 많아, 이 한 단계만으로 풀리는 경우가 실제로 꽤 많습니다.

다만 TRIM과 CLEAN은 일반 공백과 줄바꿈만 지워주고, 웹에서 복사한 데이터에 흔한 줄바꿈 없는 공백(CHAR(160))은 그대로 남는 경우가 있습니다. 이럴 때는 =SUBSTITUTE(TRIM(찾는값셀),CHAR(160),””)처럼 SUBSTITUTE를 한 겹 더 씌우면 남아있던 공백까지 제거됩니다.

5단계, 마지막 인수가 FALSE(정확히 일치)인지 확인

수식 맨 끝 인수를 비워두거나 TRUE로 두면 ‘비슷한 값’을 찾는 유사일치로 동작해 결과가 이상하게 나옵니다.

=VLOOKUP(찾는값,참조범위,열번호,FALSE) 형태로 마지막에 FALSE 또는 0을 꼭 넣어야 정확히 일치하는 값만 찾습니다.

참고로 마지막 인수를 TRUE로 두거나 생략하면 참조 표 첫 열이 반드시 오름차순으로 정렬돼 있어야 정상 동작하고, 정렬돼 있지 않으면 오류 없이 조용히 틀린 값을 반환하기도 합니다. 반대로 FALSE로 지정하면 정렬 여부와 상관없이 위에서부터 정확히 일치하는 첫 번째 값을 찾아주므로, 정렬을 신경 쓸 필요가 없다는 것도 함께 기억해두면 좋습니다.

그래도 안 풀릴 때, 원인별 해결 표

위 5단계로도 안 풀리면 아래 표에서 지금 뜬 오류 메시지를 찾아 해당 줄만 확인하면 됩니다.

증상	가능한 원인	해결 방법
#N/A	찾는 값이 참조 표 첫 열에 아예 없음	철자·띄어쓰기까지 원본과 동일한지 Ctrl+F로 재확인
#N/A	숫자가 텍스트로 저장됨	텍스트 나누기 또는 곱하기 1로 숫자 변환
#N/A	앞뒤에 보이지 않는 공백	TRIM+CLEAN 함수로 정제 후 값 붙여넣기
엉뚱한 값 반환	마지막 인수를 생략(유사일치로 동작)	마지막 인수를 FALSE 또는 0으로 지정
엉뚱한 값 반환	아래로 채우며 참조 범위가 밀림	F4로 절대참조 고정($A$2:$D$100)
#REF!	열 번호가 참조 표의 열 개수를 벗어남	col_index_num이 표 열 개수 이내인지 확인
#VALUE!	열 번호 인수에 0이나 텍스트를 입력	열 번호를 1 이상의 정수로 수정
#NAME?	함수 이름 오타 또는 따옴표 누락	수식을 다시 입력하거나 함수 마법사(Fx)로 인수 확인
결과가 0으로 표시	참조 표 해당 셀이 실제로 비어 있음	원본 데이터에 값이 채워져 있는지 원본 시트에서 확인

VLOOKUP 오류와 같이 알아두면 좋은 팁

오류를 매번 손으로 고치기보다 아예 안 나게 만드는 방법 두 가지를 같이 써보시길 권합니다.

IFERROR는 =IFERROR(VLOOKUP(…),’확인필요’)처럼 감싸면 오류 대신 원하는 문구를 표시하고, IFNA는 #N/A 오류만 골라서 처리하고 다른 오류(#REF!, #VALUE!)는 그대로 남겨 원인 파악을 쉽게 해줍니다.

Microsoft 365나 Excel 2021 이상을 쓴다면 XLOOKUP 함수로 바꾸는 것도 방법입니다. 왼쪽 열도 찾을 수 있고, 중간에 열을 추가·삭제해도 참조가 안 깨지며, 결과가 없을 때 대체값을 함수 안에서 바로 지정할 수 있어 VLOOKUP보다 오류가 훨씬 적게 납니다.

표 범위를 아예 이름 정의(수식 > 이름 관리자)로 등록해 두는 것도 실무에서 자주 쓰는 방법입니다. 참조범위 자리에 셀 주소 대신 ‘거래처표’처럼 등록해둔 이름을 넣으면, 수식을 복사·이동해도 범위가 밀릴 걱정이 없고 수식 자체도 훨씬 읽기 쉬워집니다.

반대로 일부러 유사한 값을 찾고 싶을 때는 와일드카드(*·?)를 활용할 수 있습니다. 예를 들어 =VLOOKUP(‘*사과*’,범위,2,FALSE)처럼 찾는 값에 별표를 넣으면 ‘사과’라는 글자가 포함된 첫 번째 항목을 찾아주는데, 이 기능은 마지막 인수가 FALSE일 때만 동작한다는 점도 함께 기억해 두면 좋습니다.

엑셀 온라인(웹) 버전에서 유독 VLOOKUP이 느리거나 결과가 안 뜬다면 함수 문제가 아니라 브라우저 캐시가 오래 쌓여서인 경우도 있으니 캐시부터 지워보는 것도 방법입니다.

엑셀뿐 아니라 한글 파일이 안 열릴 때도 원인을 하나씩 짚어가는 순서는 똑같습니다. 프로그램 오류는 대부분 증상을 원인별로 좁혀가면 풀립니다.

비밀번호처럼 반복적으로 뜨는 오류 메시지도 원인을 하나씩 지워나가는 방식은 같아서, 비밀번호 인증이 계속 실패할 때 확인하는 순서도 참고할 만합니다.

자주 묻는 질문

Q. VLOOKUP 대신 XLOOKUP을 꼭 써야 하나요?

A. 꼭 그럴 필요는 없습니다. 다만 왼쪽 열 찾기, 열 삽입 시 참조 유지가 필요하면 XLOOKUP이 더 안전합니다. 회사 컴퓨터가 구버전 Excel을 쓰고 있어 XLOOKUP 자체가 없다면, 위 5단계로 VLOOKUP 오류만 잡아도 실무에서는 충분합니다.

Q. IFERROR와 IFNA는 어떤 차이가 있나요?

A. IFERROR는 모든 오류를 대체값으로 바꾸고, IFNA는 #N/A만 골라 처리해 다른 오류는 그대로 남깁니다. 수식 자체에 오타가 있어도 IFERROR를 씌우면 오류가 가려져 문제를 늦게 발견할 수 있으니, 처음 수식을 만들 때는 IFERROR 없이 원인을 먼저 확인한 뒤 마지막에 씌우는 순서를 권합니다.

Q. 참조 표에 값이 분명히 있는데도 #N/A가 뜨는 이유는 뭔가요?

A. 눈에는 같아 보여도 서식(숫자·텍스트)이 다르거나 앞뒤에 보이지 않는 공백이 섞여 있는 경우가 대부분입니다. 위 1단계와 4단계를 순서대로 확인하면 원인의 대부분이 잡힙니다.

Q. VLOOKUP으로 여러 열의 값을 한 번에 가져올 수 있나요?

A. 함수 하나로는 한 번에 한 열만 가져올 수 있어서, 열 번호만 바꿔 수식을 옆으로 복사하는 방식을 씁니다. 다만 이렇게 하면 열 번호가 하드코딩돼 표 구조가 바뀔 때 값이 밀릴 수 있으니, 열 번호 자리에 =MATCH(가져올항목명,머리글행,0)을 넣어 두면 머리글 이름으로 자동으로 열을 찾아주기 때문에 표에 열이 추가돼도 수식을 다시 손볼 필요가 없습니다.

이 순서대로 확인해도 원인이 안 잡힌다면 공식 도움말도 참고해 보세요. Microsoft 공식 지원 문서에서 더 자세한 사례를 확인할 수 있습니다.

여기까지 VLOOKUP 오류를 원인별로 짚어봤습니다. 다음에는 XLOOKUP과 INDEX·MATCH 조합을 실전 예시로 비교해 볼 예정이니, 혹시 VLOOKUP 대신 어떤 함수를 쓰고 계신가요? 댓글로 알려주세요.

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