메타 설명
VLOOKUP 오류가 날 때 우선 확인할 6가지를 단계별로 정리합니다. VLOOKUP #N/A·#REF!·엉뚱한 값 문제의 원인과 실제 수정 예시, 체크리스트를 제공합니다. 실제 예시와 실수 방지 체크리스트까지 확인해 바로 적용할 수 있습니다.
엑셀에서 VLOOKUP 오류가 발생하면 작업이 멈추고 원인을 찾기 어렵습니다. 특히 VLOOKUP 오류와 VLOOKUP #N/A 메시지를 마주했을 때, 어떤 순서로 무엇을 점검해야 하는지 모르면 시간만 낭비됩니다.
이 글은 실제 사례와 계산 예시, 체크리스트를 통해 #N/A·#REF!·부정확한 값이 나올 때 우선 점검해야 할 6가지를 설명합니다. 순서대로 점검하면 대부분의 문제를 빠르게 해결할 수 있습니다.
핵심 개념: VLOOKUP 네 인수 구조
VLOOKUP 함수의 기본 구조와 네 인수를 정확히 이해하는 것이 문제 해결의 시작입니다.
네 인수(인자)의 의미
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: 찾으려는 값(예: A2)
- table_array: 검색할 표 범위(예: $D$2:$F$100)
- col_index_num: 결과로 반환할 열 번호(테이블의 첫 열을 1로 계산)
- range_lookup: TRUE(근사값) 또는 FALSE(정확값). 보통 정확 일치를 위해 FALSE를 사용
중요 규칙 요약
특히 유의할 점: 조회할 열(검색 키)은 table_array의 첫 열이어야 합니다. col_index_num은 table_array의 열 수를 초과하면 #REF!가 발생합니다. 정확 일치를 원하면 반드시 FALSE를 사용하세요.
먼저 확인할 6가지 (우선순위 순)
- 조회 열이 table_array의 첫 열인지 확인
- range_lookup이 정확 일치인지(FALS E) 확인
- 데이터 형식과 보이지 않는 공백(공백/문자형 vs 숫자형) 확인
- 절대 참조($)로 table_array 고정 여부 확인
- col_index_num 값이 table_array 열 수를 초과하지 않는지(#REF! 점검)
- IFERROR로 숨기기 전에 원인 진단 여부 확인
1) 조회 열(lookup column)은 항상 table_array의 첫 열
예: =VLOOKUP(A2,$D$2:$F$100,2,FALSE)에서 A2의 값을 찾으려면 D열이 검색 키가 되어야 합니다. 만약 검색 키가 E열에 있다면 table_array를 $E$2:$F$100:$D$2: 형태로 재정렬하거나 INDEX/MATCH 조합을 사용해야 합니다.
2) FALSE(정확 일치)를 먼저 고려하세요
range_lookup을 생략하거나 TRUE로 설정하면 근사값을 사용하며 table_array의 첫 열이 오름차순으로 정렬되어 있어야 합니다. 정렬되지 않은 데이터에서 TRUE를 쓰면 엉뚱한 값이 나옵니다. 정확한 매칭을 원하면 항상 FALSE를 권장합니다.
데이터 형식·공백 문제와 해결 방법
가장 흔한 원인 중 하나는 데이터 형식 불일치(예: 한 쪽은 숫자, 다른 쪽은 텍스트)나 눈에 보이지 않는 공백입니다.
| 증상 | 원인 | 해결 방법 |
|---|---|---|
| #N/A | 값 불일치(형식 또는 공백) | TRIM, VALUE, CLEAN로 정리 후 재검색 |
| 엉뚱한 값 반환 | range_lookup이 TRUE이면서 정렬 불일치 | 정렬하거나 FALSE로 변경 |
| #REF! | col_index_num이 범위 초과 | col_index_num 조정 또는 table_array 확장 |
문자열 공백 제거 예시: =TRIM(SUBSTITUTE(A2,CHAR(160),” “)) — 웹에서 복사한 데이터에 비-breaking space(CHAR(160))가 섞이는 경우가 있습니다. 숫자처럼 보이는 텍스트를 숫자로 바꿀 때는 =VALUE(TRIM(A2)).
절대 참조와 복사 시 발생하는 오류
여러 셀로 수식을 복사할 때 table_array를 상대 참조로 두면 범위가 틀어집니다. 반드시 $를 사용해 고정하세요.
- 올바른 예: =VLOOKUP($A2,$D$2:$F$100,2,FALSE)
- 오류 유발 예: =VLOOKUP(A2,D2:F100,2,FALSE) — 복사하면 범위가 이동
자주 하는 실수
- table_array를 선택할 때 열 순서를 바꿔서 조회 열이 첫 열이 아님
- col_index_num을 전체 시트 열 번호로 착각(테이블의 첫 열을 1로 계산해야 함)
- range_lookup을 생략하여 근사값이 동작하도록 한 뒤 정렬을 확인하지 않음
- IFERROR로 오류를 숨기고 근본 원인을 확인하지 않음
실제 계산 예시: 단계별 진단
가정: 고객 ID로 이름을 찾는 작업. ID는 A열에 있고 참조표는 D:E에 있어 이름은 E열에 있습니다.
- 기본 수식: =VLOOKUP(A2,$D$2:$E$100,2,FALSE)
- 결과가 #N/A이면 아래를 차례로 점검:
- D열에서 A2 값이 실제로 존재하는지 찾기(검색: Ctrl+F)
- 데이터 형식 확인: =TYPE(A2) 와 =TYPE(D2) 비교
- 공백 확인: =LEN(A2) 와 =LEN(TRIM(A2)) 비교
- 만약 #REF!이면 col_index_num(여기선 2)가 table_array의 열 수(여기선 2)를 초과하는지 확인
예시: A2=1002, 참조표 D2:D100에는 “1002 ” (끝에 공백)로 저장되어 있으면 VLOOKUP은 찾지 못합니다. 해결: 참조표의 값을 TRIM으로 정리하거나 수식에 TRIM을 사용하되 성능을 고려해 원본을 정리하는 편이 좋습니다.
IFERROR는 문제를 숨기지 말고 원인 확인 후 사용
IFERROR는 오류를 사용자 친화적으로 처리할 때 유용하지만 원인을 확인하기 전에는 사용하지 마세요. 원인을 모른 채 IFERROR로 감추면 데이터 누락이나 논리적 결함을 놓칩니다.
예시(오류 확인 후 사용):
- 문제 진단 중: =VLOOKUP(A2,$D$2:$E$100,2,FALSE) — 오류가 발생하면 원인 점검
- 문제 해결 후 사용자 표시용: =IFERROR(VLOOKUP(A2,$D$2:$E$100,2,FALSE),”조회불가”)
추가 점검 항목: 정렬, 중복, 사례별 조언
근사값(=TRUE일 때)을 쓰는 경우 table_array의 첫 열은 오름차순 정렬되어 있어야 합니다. 중복 키가 있는 경우 VLOOKUP은 첫 번째 일치 항목만 반환합니다. 중복 키를 허용할 수 없는 상황이면 중복 제거 또는 INDEX/MATCH(또는 XLOOKUP) 사용을 고려하세요.
| 문제 | 원인 | 권장 해결책 |
|---|---|---|
| 엉뚱한(근사) 값 | range_lookup=TRUE이면서 정렬 안 됨 | FALSE로 변경하거나 정렬 |
| 값이 보이는데 #N/A | 데이터 형식 불일치 / 공백 | TRIM, VALUE, 일괄 포맷 |
| #REF! | col_index_num 초과 | col_index_num 수정 |
참고: 일부 기능 설명과 오류 원인은 Microsoft 문서를 기반으로 정리했습니다. 화면·버전에 따라 메뉴 위치나 일부 동작이 다를 수 있습니다(2026년 8월 기준). 변동 가능성이 있는 메뉴나 서비스 조건에는 화면·버전에 따라 차이가 날 수 있음을 명시합니다.
공식 참고: Microsoft Support — VLOOKUP function
공식 참고: Microsoft Support — How to correct a #N/A error
자주 묻는 질문
Q1: VLOOKUP이 #N/A를 반환하는데 값은 분명히 존재합니다. 가장 빠르게 확인할 것은?
A1: 먼저 조회 열이 table_array의 첫 열인지 확인하고, 두 번째로 데이터 형식(숫자 vs 텍스트)과 보이지 않는 공백을 점검하세요. TRIM, VALUE 함수로 정리한 후 재시도하면 대부분 해결됩니다.
Q2: col_index_num 때문에 #REF!가 뜨는데 왜 그런가요?
A2: col_index_num은 table_array에서 몇 번째 열을 반환할지 지정합니다. 예를 들어 table_array가 $D$2:$E$100이면 col_index_num은 1 또는 2만 가능합니다. 3 이상을 넣으면 #REF!가 발생합니다. 해결은 col_index_num을 조정하거나 table_array 범위를 확장하는 것입니다.
Q3: IFERROR로 #N/A를 숨겨도 되나요?
A3: 일시적으로는 가능하지만 권장하지 않습니다. IFERROR는 원인을 숨기기 때문에 데이터 누락, 입력 오류, 범위 문제 등 근본 원인을 찾지 못하게 합니다. 먼저 원인을 진단한 뒤 사용자 메시지를 보여줄 목적으로 IFERROR를 사용하세요.
Q4: VLOOKUP 대신 어떤 함수(또는 조합)를 고려해야 하나요?
A4: 조회 열이 오른쪽에 있거나 복잡한 조건이 필요하면 INDEX/MATCH 조합을 사용하세요. 최신 Excel에서는 XLOOKUP이 더 직관적이며 왼쪽/오른쪽 상관없이 검색이 가능합니다. 다만 회사 표준이나 호환성 때문에 VLOOKUP을 계속 써야 한다면 위의 6가지 점검 순서를 따르세요.
COSTOCK 편집 기준