스타트업 회계팀에서 직원 급여 대장과 부서 코드표를 VLOOKUP으로 연결하다가 #N/A 오류가 줄줄이 뜬 적이 있다. 분명 두 표에 같은 값이 있는데도 엑셀은 못 찾는다고 우겼다.
이 글은 급하게 원인을 찾아야 하는 실무자를 위해, 엑셀에서 VLOOKUP #N/A 오류가 뜨는 이유와 바로 고치는 순서를 정리한 글이다. 표 두 개를 이어 붙여 데이터를 조회할 때 자주 막히는 지점 위주로 썼다.
VLOOKUP #N/A 오류, 어떻게 해야 바로 해결될까
가장 빠른 해결책은 찾는 값과 표의 값이 같은 형식(텍스트인지 숫자인지)부터 확인하는 것이다. 실무에서 겪어본 오류 중 7할 이상은 한쪽은 텍스트, 한쪽은 숫자로 저장돼 있어서 생겼다.
다음으로 자주 놓치는 건 셀 앞뒤에 붙은 눈에 안 보이는 공백이다. 이 두 가지만 확인해도 대부분의 #N/A는 사라진다. 그래도 안 풀리면 아래 단계별 방법과 원인별 표를 순서대로 따라가면 된다.
VLOOKUP #N/A 오류 해결하는 단계별 방법
스크린샷 없이도 메뉴 경로만 보고 따라 할 수 있게 순서대로 적었다.
- 값 형식부터 통일한다. 데이터 탭 > 데이터 도구 > 텍스트 나누기를 누르고, 1단계 마법사에서 구분 기호 선택 없이 바로 마침을 누르면 텍스트로 저장된 숫자가 진짜 숫자로 바뀐다.
- 공백을 제거한다. 빈 셀에 =TRIM(CLEAN(A2))를 입력해 값을 정리하고, 이 결과를 VLOOKUP의 찾는 값으로 다시 연결한다.
- 범위를 고정한다. 표 범위를 지정할 때 F4 키를 눌러 $A$2:$B$100처럼 절대 참조로 바꾼다. 수식을 아래로 채우다 범위가 밀리면 #N/A가 뜬다.
- 정확히 일치 옵션을 확인한다. 수식 마지막 인수가 FALSE 또는 0인지 본다. 이 인수를 생략하거나 TRUE로 두면 비슷한 값을 찾다가 엉뚱한 결과나 오류가 난다.
- 이름 관리자에서 범위를 점검한다. 수식 탭 > 정의된 이름 > 이름 관리자에서 참조 범위가 실제 표와 어긋나 있지 않은지 확인한다.
- 그래도 안 되면 IFERROR로 감싼다. =IFERROR(VLOOKUP(A2,$D$2:$E$100,2,FALSE),”확인필요”) 형태로 감싸면 오류 대신 원하는 문구가 뜨고, 어느 행에서 문제가 생겼는지 한눈에 걸러낼 수 있다.
분명히 있는 값인데 왜 못 찾을까, 원인별 해결 표
같은 #N/A라도 원인에 따라 고치는 방법이 다르다. 아래 표로 내 상황과 먼저 비교해본다.
| 원인 | 확인 방법 | 해결 방법 |
|---|---|---|
| 숫자 vs 텍스트 | 셀이 왼쪽 정렬이면 텍스트, 오른쪽 정렬이면 숫자 | 텍스트 나누기 또는 VALUE 함수로 형식 통일 |
| 앞뒤 공백 | 셀을 더블클릭해 커서로 직접 확인 | TRIM 함수로 공백 제거 후 재조회 |
| 범위 밀림 | 수식을 아래로 채웠을 때 범위 시작 행이 바뀌는지 확인 | F4로 절대 참조 고정 |
| 대략적 일치 설정 | 마지막 인수가 비어 있거나 TRUE인지 확인 | FALSE 또는 0으로 변경 |
| 찾는 열이 기준 열보다 왼쪽 | VLOOKUP은 항상 왼쪽에서 오른쪽으로만 찾음 | 표 순서를 바꾸거나 INDEX MATCH로 전환 |
| 실제로 값이 없음 | Ctrl+F로 찾는 값을 표에서 직접 검색 | 원본 데이터 입력 여부부터 확인 |
INDEX MATCH나 XLOOKUP도 같이 알아두면 좋을까
결론부터 말하면, VLOOKUP만 고집할 필요는 없다. 표 구조가 자주 바뀌는 파일이라면 INDEX와 MATCH를 조합하거나, Microsoft 365 구독 버전이라면 XLOOKUP 함수를 쓰는 편이 오류에 더 강하다.
| 함수 | 왼쪽 열 찾기 | 정확히 일치 기본값 | 비고 |
|---|---|---|---|
| VLOOKUP | 불가능 | 아니오(TRUE가 기본) | 오래된 버전 파일에서도 동일하게 작동 |
| INDEX+MATCH | 가능 | 직접 지정 | 수식이 길지만 유연함 |
| XLOOKUP | 가능 | 예(기본이 정확히 일치) | Microsoft 365 구독 버전에서 사용 가능 |
매달 구조가 똑같은 급여표는 VLOOKUP만으로 충분했다. 반대로 부서 코드가 자주 추가되는 표는 XLOOKUP으로 바꾼 뒤 #N/A 발생 빈도가 눈에 띄게 줄었다.
엑셀 파일 자체가 자주 멎거나 함수 계산이 느려진다면 프로그램 문제일 수도 있다. 이럴 때는 윈도우 업데이트 오류로 프로그램이 자꾸 멈출 때의 해결 순서를 먼저 점검해보는 것도 방법이다. 한글에서 표를 옮겨 붙이다 비슷하게 값이 어긋나는 경우가 있다면 한글 표 만들기와 표 안 될 때 해결법도 참고할 만하다.
더 자세한 공식 설명은 마이크로소프트 공식 지원 문서에서도 확인할 수 있다.
자주 묻는 질문
Q. VLOOKUP과 XLOOKUP 중 어떤 걸 써야 하나요?
표 구조가 자주 바뀌거나 왼쪽 열을 찾아야 한다면 XLOOKUP이 유리하다. 다만 오래된 버전과 파일을 주고받는다면 VLOOKUP이 호환성 면에서 더 안전하다.
Q. IFERROR로 오류만 가리면 문제가 해결되나요?
아니다. IFERROR는 오류 표시만 바꿀 뿐 근본 원인은 그대로 남는다. 형식과 공백부터 먼저 확인하고, 마지막 안전장치로만 사용하는 게 맞다.
Q. 텍스트로 저장된 숫자인지 어떻게 빠르게 확인하나요?
셀 값이 왼쪽 정렬돼 있으면 텍스트일 가능성이 높다. 빈 셀에 =ISNUMBER(A2)를 입력해 FALSE가 나오면 텍스트로 저장된 것이다.
지금까지 VLOOKUP #N/A 오류의 원인과 해결 순서를 정리했다. 형식 통일과 공백 제거만으로도 대부분 풀리니 당황하지 말고 표부터 다시 확인해보길 권한다. 여러분은 VLOOKUP 오류를 주로 어떤 방법으로 해결하고 있는지 궁금하다.
출처: Editlab, https://editlab.luvpp.com
오래 쓴 사람의 요령이 따로 있습니다. 같은 것을 찾아본 사람들이 모여 있는 곳이 있습니다. 글은 누구나 읽을 수 있고, 질문을 남기려면 카페 가입이 필요합니다.
EDITLAB 뉴스레터
오늘의 한 가지
평일 아침, 오늘 꼭 알아야 할 정보 하나. 신청 마감이 코앞인 지원금, 검색해도 안 나오는 실제 절차와 후기.
생년월일을 넣으면 매주 월요일 내 주간 운세도 같이 받을 수 있습니다.
개인정보처리방침 · 문의 editlab204@gmail.com · 발행 Editlab
