직장인들이 실무에서 가장 많이 사용하는 엑셀 함수를 꼽으라면 단연코 VLOOKUP(브이룩업)일 것입니다. 저도 입사하고 얼마 안되어 시간이 오래 걸리는 데이터 산출 작업을 간단히 하기 위해 엑셀 브이룩업을 활용해야만 했는데요. 방대한 데이터 속에서 원하는 값을 쏙쏙 뽑아주는 마법 같은 함수이지만, 수식을 입력하고 엔터를 치는 순간... #N/A 라는 에러 메시지가 뜨면 눈앞이 캄캄해지곤 했습니다.
엑셀 초보자분들은 "분명히 눈으로 봤을 땐 데이터가 있는데 대체 왜 못 찾는 거지?" 라며 당황하기 쉽습니다. 오늘은 직장인들의 소중한 칼퇴를 방해하는 주범, 엑셀 VLOOKUP #N/A 오류가 발생하는 4가지 핵심 이유와, 이를 1분 만에 완벽하게 해결하는 방법을 단계별로 알아보겠습니다.
① 엑셀 VLOOKUP #N/A 오휴, 대체 의미가 뭘까?
해결법을 알아보기 전에 에러의 의미부터 짚고 넘어갈 필요가 있습니다. 여기서 #N/A 는 "Not Available"(사용할 수 없음)의 약자로, 엑셀이 '네가 찾으라고 한 값을 지정된 범위에서 찾을 수 없다'고 말해주는 일종의 경고 메시지입니다.
단순히 데이터가 누락이 되어서 뜨는 경우라면 정상적인 반응이지만, 두 눈으로 확인했을 때 분명히 값이 존재함에도 이 오류가 뜬다면 아래의 4가지 원인 중 하나일 확률이 99% 입니다.
① 마지막 옵션 '정확히 일치(0)'를 빠뜨렸을 때
초보자들이 가장 흔하게 하는 실수 첫 번째는 바로 수식의 마지막 인수를 생략하는 것 입니다.
VLOOKUP 함수의 기본 구조는 =VLOOKUP(찾을 값, 참조범위, 열번호, [옵션])으로 이루어져 있습니다.
여기서 대괄호로 표시된 마지막 '옵션' 자리에 0 또는 FALSE 를 입력하지 않으면 어떻게 될까요?
엑셀은 기본적으로 '유사 일치(1 또는 TRUE)로 값을 찾으려 시도합니다. 이 경우 참조 범위의 데이터가 오름차순으로 정렬되어 있지 않다면, 엑셀이 헤매다가 엉뚱한 값을 가져오거나 엑셀 VLOOKUP #N/A 오류를 뱉어내게 됩니다.
해결 방법 : 수식을 작성할 때 맨 마지막에 습관적으로, 0 또는 FALSE 를 넣어주세요. 사번이나 이름, 품번 등 정확하게 일치하는 값을 찾아야 하는 실무에서는 무조건 0을 쓴다고 외워두시면 편합니다.
예시 : =VLOOKUP(A2, C:E, 2, 0)
② 숫자와 텍스트 서식이 불일치할 때
분명히 똑같은 '1004'라는 사번을 찾고 있는데 오류가 난다면? '데이터 서식'을 의심해 봐야 합니다. 한쪽 셀의 1004는 계산이 가능한 '숫자'로 인식되어 있고, 다른 한쪽의 1004는 단순히 글자인 '텍스트'로 인식되어 있다면, 엑셀은 이 둘을 완전히 다른 데이터로 취급합니다. ERP나 인트라넷 같은 외부 시스템에서 엑셀로 데이터를 다운로드 했을 때 아주 빈번하게 발생하는 문제입니다.
해결 방법 : 두 데이터의 표시 형식을 하나로 통일해야 합니다. 눈으로 구별하는 팁은 텍스트로 인식된 숫자는 셀의 왼쪽 모서리에 초록색 삼각형 점이 생깁니다.
1. 데이터가 있는 범위를 드래그합니다.
2. 옆에 뜨는 '노란색 느낌표 아이콘'을 클릭합니다.
3. '숫자로 변환'을 클릭해주면 서식이 통일되면서 오류가 즉시 해결됩니다.
③ 눈에 보이지 않는 공백(띄어쓰기)이 숨어있을 때
사람의 눈에는 "홍길동"과 "홍길동 " (뒤에 띄어쓰기 1칸)이 똑같아 보일 수 있지만, 오점 하나라도 다 잡아내는 엑셀 로봇에게는 글자 수가 다른 완전히 별개의 데이터입니다. 복사 붙여넣기 하는 과정에서 쓸데없는 공백이 묻어들어와 매칭을 방해하는 것이죠.
해결 방법 : TRIM 또는 함수 바꾸기가 있습니다.
1. 빈 열을 하나 만들고 =TRIM(텍스트셀) 함수를 적용하면 앞뒤의 불필요한 띄어쓰기가 마법처럼 싹 지워집니다.
2. 텍스트 범위를 지정한 후 Ctrl + H (찾기 및 바꾸기) 를 누릅니다. '찾을 내용'에 스페이스바를 한번 눌러 띄어쓰기를 입력하고, '바꿀 내용'은 아무것도 적지 않은 상태로 '모두 바꾸기'를 클릭합니다. 그러면 데이터 안의 모든 공백이 제거되어 에러가 풀립니다.
④ 찾을 값이 참조 범위의 첫 번째 열이 아닐 때
이 부분은 VLOOKUP 함수가 가진 태생적인 한계점입니다. VLOOKUP은 값을 찾을 때 지정한 참조 범위의 무조건 '첫 번째 열(가장 왼쪽 열)'에서만 기준값을 검색합니다.
예를 들어 내가 '이름'을 기준으로 '부서명'을 찾고 싶은데, 참조 범위로 지정한 표의 1열이 '사번'이고 2열이 '이름'이라면?
엑셀 1열인 사번들 중에서 '이름'을 찾으려다가 실패하고 엑셀 VLOOKUP #N/A 오류를 내뿜게 됩니다.
해결 방법 : 수식의 두 번째 인수인 '참조 범위'를 다시 드래그해야 합니다. 기준값이 무조건 표의 1열이 되도록 드래그를 시작하세요. 만약 데이터 구조상 열의 순서를 바꿀 수 없다면, VLOOKUP 대신 방향에 구애받지 않는 INDEX & MATCH 함수 조합이나 최신 버전의 XLOOKUP 함수를 사용하는 것을 추천합니다.
노하우 대방출!
진짜 값이 없을 때 #N/A 를 깔끔하게 숨기는 방법 (IFERROR)
지금까지는 실수로 발생한 오류를 잡는 법이었다면, 이번엔 '진짜로 매칭되는 데이터가 없을 때'의 노하우입니다. 예를 들면, 신규 입사자라서 아직 부서 데이터가 없는 경우 당연히 #N/A 가 뜨는 게 맞지만 보고서에 에러 메시지가 그대로 노출되면 서류가 아주 지저분해 보입니다. 이럴 때는 IFERROR 함수를 씌워주면 깔끔하게 해결됩니다.
적용 수식 : =IFERROR(VLOOKUP(A2, C:E, 2, 0), "미등록")
이렇게 수식 안에 수식을 적용하여 감싸주면, 에러가 날 자리에는 지저분한 영문 대신 "미등록", "정보없음" 혹은 공백(" ")으로 표시할 수 있어 실무 보고서의 퀄리티가 확 올라갑니다.
마치며
오늘은 실무에서 가장 자주 마주치는 엑셀 VLOOKUP #N/A 오류가 뜨는 이유와 4가지 해결 방법에 대해 정리해 보았습니다. 옵션 값 생략, 서식 불일치, 숨은 공백, 참조 범위의 위치 등을 숙지하며, 조금만 주의를 기울이면 누구나 쉽게 해결할 수 있는 문제들입니다.
당황 보다는 시간을 가지고 알아보는 시간을 가지며, 오늘 알려드린 4가지 체크리스트들을 순서대로 확인해 보세요. 이 글을 즐겨찾기 해두셨다가 엑셀 작업을 할 때 막힐 때 마다 꺼내 보시길 바라며, 전국 모든 직장인 분들의 칼퇴를 응원합니다!
