엑셀 VLOOKUP #N/A 오류, 딱 3가지만 확인하면 1분 만에 해결!

안녕하세요, 여러분의 칼퇴를 돕는 IT 실전 가이드 데브프리입니다.

 

회사에서 보고서를 쓰거나 대량의 데이터를 정리할 때 가장 자주 쓰는 함수를 꼽으라면 단연 VLOOKUP일 것입니다. 그런데 기분 좋게 수식을 입력하고 엔터를 쳤을 때, 기다렸던 데이터 대신 무시무시한 #N/A 오류가 셀을 가득 채우면 등은 식은땀이 흐르기 마련입니다. 눈으로 보기에는 분명히 있는 데이터인데 왜 자꾸 없다고 나오는 걸까요?

 

개발자에게 버그 리포트가 있다면, 직장인에게는 #N/A 오류가 있습니다. 이 오류는 'Not Available'의 약자로, 쉽게 말해 "네가 찾으라고 한 값이 참조하는 표에 아무리 찾아도 없다"는 뜻입니다.

 

오늘 이 글에서는 여기저기 수식을 다 뒤엎을 필요 없이, 딱 3가지 원인만 체크해서 1분 만에 #N/A 오류를 완벽하게 해결하는 방법을 알려드리겠습니다. 지금 바로 여러분의 엑셀 창을 켜고 따라 해보세요!

 

1. 첫 번째 체크: 찾는 값이 참조 범위의 '첫 번째 열'에 있는가?

VLOOKUP 함수를 쓸 때 가장 많은 분이 하는 첫 번째 실수입니다. 엑셀의 VLOOKUP 함수는 내가 지정한 참조 범위의 무조건 '첫 번째 열(가장 왼쪽 열)'에서만 값을 찾는 엄격한 규칙이 있습니다.

기준이 되는 데이터(찾을 값)는 반드시 빨간색 참조 범위의 가장 첫 번째 열에 위치해야 합니다.

💡 해결 방법

만약 이름으로 부서명을 찾고 싶다면, 참조 범위(Table_array)를 선택할 때 '이름'이 있는 열부터 시작하도록 범위를 다시 지정해 주어야 합니다. 사원번호부터 범위를 잡고 이름으로 찾으려고 하면 엑셀은 절대로 값을 찾아내지 못하고 #N/A를 뱉어냅니다.

 

2. 두 번째 체크: 데이터 타입(텍스트 vs 숫자)이 일치하는가?

"내 눈에는 분명히 똑같은 '101'인데 왜 없다고 나오죠?" 하시는 분들의 99%는 이 문제입니다. 사람은 눈으로 보면 둘 다 똑같은 숫자 101로 보이지만, 엑셀은 '숫자 101'과 '문자(텍스트) 101'을 완전히 다른 데이터로 인식합니다.

주로 사원번호, 상품코드, 회원번호 등을 다른 시스템에서 다운로드하여 엑셀에 붙여넣었을 때 이런 타입 불일치 현상이 자주 발생합니다.

데이터 형식이 다르면 눈에 똑같이 보여도 #N/A 오류가 발생합니다.

💡 해결 방법

1. 찾을 값이나 참조 범위의 셀 왼쪽 상단에 초록색 작은 삼각형이 있는지 확인합니다.
2. 해당 셀들을 드래그하여 선택한 뒤, 나타나는 노란색 경고창 아이콘을 클릭합니다.
3. [숫자로 변환]을 클릭해 주면 타입이 통일되면서 오류가 마법처럼 사라집니다.

 

3. 세 번째 체크: 눈에 보이지 않는 '공백(Space)'이 있는가?

가장 대수롭지 않게 넘겼다가 뒤통수를 맞는 원인입니다. 데이터 뒤에 눈에 보이지 않는 스페이스바 공백이 한 칸만 들어가 있어도 엑셀은 다른 글자로 판단합니다. 즉, "홍길동"과 "홍길동 "은 전혀 다른 데이터인 것이죠.

글자 앞뒤에 보이지 않는 공백이 포함되어 있으면 정확한 값을 찾을 수 없습니다.

💡 해결 방법 (TRIM 함수 활용)

공백을 일일이 지우기 힘들다면 공백을 자동으로 깎아주는 TRIM 함수를 VLOOKUP 안에 중첩해서 쓰면 간단히 해결됩니다.

• 기존 수식: =VLOOKUP(A2, 참조범위, 2, FALSE)
• 변경 수식: =VLOOKUP(TRIM(A2), 참조범위, 2, FALSE) 이렇게 수식을 감싸주면 찾을 값 앞뒤의 모든 불필요한 공백을 제거한 상태로 검색하기 때문에 깔끔하게 매칭됩니다.

🛠️ 보너스 팁: 오류 창을 깔끔하게 숨기는 IFERROR 함수

모든 방법을 동원해도 원본 데이터 자체에 진짜 그 값이 없어서 #N/A가 뜨는 경우도 있습니다. 이때 보고서의 미관을 해치는 #N/A 글자를 깔끔하게 빈칸이나 다른 문구로 대체하고 싶다면 IFERROR 함수와 함께 사용하세요.

• 수식 형태: =IFERROR(VLOOKUP수식, "오류시 출력할 문구")
• 예시: =IFERROR(VLOOKUP(A2, B:D, 3, FALSE), "데이터 없음")

이렇게 감싸주면 값이 없을 때 #N/A 대신 "데이터 없음" 혹은 공백("")으로 표시되어 훨씬 깔끔한 보고서를 완성할 수 있습니다.

 

마무리

오늘 알아본 1. 첫 번째 열 기준 범위 설정, 2. 데이터 타입 일치, 3. 보이지 않는 공백 제거 이 3가지만 명심하시면 실무에서 발생하는 VLOOKUP #N/A 오류의 95% 이상은 누구나 1분 만에 해결할 수 있습니다.

 

데이터가 꼬여서 풀리지 않을 때는 당황하지 말고 엑셀 로봇의 입장에서 차근차근 데이터 구조를 들여다보는 것이 칼퇴로 가는 지름길입니다. 오늘 가이드가 도움이 되셨다면 좋겠습니다. 수식을 적용하다가 잘 안되는 부분이 있다면 언제든 오류 현상을 댓글로 남겨주세요!

 

지금까지 데브프리였습니다. 감사합니다!