수식을 여러 번 고쳐도 #N/A가 사라지지 않는다면, 범인은 수식이 아니라 데이터일 가능성이 높다. 엑셀 브이룩업 오류 해결 방법의 핵심은 원인 진단이다 — 오류 코드마다 원인이 다르므로, 코드를 확인한 뒤 해당 원인을 제거하면 대부분 한 번에 해결된다. 같은 #N/A라도 공백 문자 때문일 수도 있고, 숫자·텍스트 형식 불일치 때문일 수도 있다. 원인을 모른 채 수식만 반복해서 수정하면 시간만 낭비된다.
#N/A 오류 — VLOOKUP 불만의 대부분이 여기서 비롯된다
#N/A는 “값을 찾지 못했다”는 신호다. 분명히 데이터에 있는 값인데도 #N/A가 뜬다면 아래 다섯 가지를 순서대로 확인해야 한다.
1. 눈에 보이지 않는 공백 문자
복사·붙여넣기나 외부 시스템 내보내기로 가져온 데이터에는 셀 앞뒤에 공백이 숨어 있는 경우가 많다. 화면에서는 “서울”과 “서울 “(뒤에 공백 포함)이 똑같이 보이지만, VLOOKUP은 두 값을 다른 문자열로 인식한다. TRIM() 함수로 검색값과 범위 데이터를 모두 정리한 뒤 다시 시도한다. 특히 ERP나 회계 시스템에서 내려받은 코드·ID 데이터에서 자주 발생한다.
2. 숫자 vs. 텍스트 형식 불일치
제품코드·사원번호처럼 숫자처럼 생긴 값이 한쪽은 숫자 형식, 다른 쪽은 텍스트 형식으로 저장된 경우다. 셀을 선택했을 때 왼쪽 정렬이면 텍스트, 오른쪽 정렬이면 숫자다. 셀 왼쪽 상단에 초록색 삼각형이 보이면 텍스트로 저장된 숫자라는 경고 표시다. VALUE()로 텍스트를 숫자로, TEXT()로 숫자를 텍스트로 변환해 형식을 통일한다.
3. 범위의 첫 번째 열이 검색 기준 열이 아닐 때
VLOOKUP은 지정한 범위의 맨 왼쪽 열에서만 검색한다. B:D를 범위로 지정하면 B열에서 찾는다. 찾으려는 기준값이 C열이나 D열에 있다면 VLOOKUP으로는 해결이 불가능하다. 이 경우 범위를 기준 열 포함하도록 재설계하거나, INDEX+MATCH 또는 XLOOKUP으로 전환해야 한다.
4. 4번째 인수(range_lookup) 생략 또는 TRUE 설정
4번째 인수를 생략하면 기본값은 TRUE(근사값 검색)다. 근사값 검색은 범위가 오름차순으로 정렬되어 있다고 전제하는데, 정렬이 안 된 상태에서 TRUE를 쓰면 엉뚱한 결과나 #N/A가 나온다. 정확한 일치를 원한다면 반드시 FALSE 또는 0을 명시한다. 이 인수를 생략하는 습관이 #N/A의 원인인 경우가 생각보다 많다.
5. 검색값이 범위 내 최솟값보다 작을 때 (TRUE 모드 한정)
근사값 검색(TRUE) 모드에서는 검색값이 범위 첫 번째 행의 값보다 작으면 #N/A를 반환한다. 세금 구간표나 할인율 테이블처럼 TRUE 모드를 의도적으로 쓰는 경우라면, 범위 시작 행의 값이 가능한 최솟값을 커버하는지 확인해야 한다.
#REF!와 #VALUE! — 데이터가 아닌 수식 구조 문제다
#REF! 오류: 열 번호가 범위를 벗어났다
세 번째 인수 col_index_num이 지정한 범위의 열 수보다 클 때 발생한다. 예를 들어 B:D(3열)를 범위로 쓰면서 col_index_num을 4로 입력하면, 참조할 네 번째 열이 존재하지 않아 #REF!가 뜬다. 열을 삽입하거나 삭제한 뒤 갑자기 오류가 생겼다면 거의 이 케이스다. 범위를 늘리거나 col_index_num을 조정해야 한다.
#VALUE! 오류: col_index_num 인수 타입이 틀렸다
col_index_num에 숫자 대신 텍스트가 들어가거나 0 이하의 값이 입력되면 #VALUE!가 발생한다. 다른 셀을 참조해 열 번호를 동적으로 계산하는 구조에서, 해당 셀이 비어 있거나 문자열이면 생기는 오류다. MATCH()로 열 번호를 자동 계산하는 수식이라면 MATCH 결과 자체가 정상인지 별도 셀에서 먼저 확인한다.
오류 코드 없이도 값이 틀릴 때 — 더 위험한 케이스
오류 코드가 없으니 그냥 넘어가기 쉽다. 그러나 잘못된 값을 맞는 값으로 착각하고 보고서나 집계표에 올리면 나중에 훨씬 큰 문제가 된다.
- 범위를 절대 참조로 고정하지 않았다 — 수식을 아래로 채울 때 범위가 함께 밀린다. 범위 인수는 반드시
$B$2:$D$100처럼 $ 기호를 붙여야 한다. - 검색값이 범위에 중복 존재한다 — VLOOKUP은 검색값이 여러 행에 있어도 맨 위 행 하나의 값만 반환한다. 같은 코드가 두 행 이상 있다면 의도와 다른 결과가 나올 수 있다.
- TRUE 모드인데 범위가 정렬되지 않았다 — 근사값 검색은 아무 경고 없이 엉뚱한 행의 값을 반환한다. 정렬 상태를 확인하거나, 정렬이 보장되지 않으면 FALSE 모드로 전환한다.
VLOOKUP의 구조적 한계 — XLOOKUP과 INDEX+MATCH가 필요한 시점
VLOOKUP은 왼쪽→오른쪽 방향으로만 검색한다. 범위 중간에 열이 삽입되면 col_index_num을 일일이 수동으로 고쳐야 한다. 이 두 가지가 반복적으로 문제가 된다면 대안 함수를 고려할 때다.
| 함수 | 장점 | 제약 |
|---|---|---|
| VLOOKUP | 거의 모든 버전 지원, 익숙한 구조 | 왼쪽 방향 검색 불가, 열 삽입에 취약 |
| INDEX+MATCH | 양방향 검색, 열 구조 변경에 강함 | 수식이 길어짐, 진입 장벽 있음 |
| XLOOKUP | 양방향, 기본값 설정 간단, 가장 직관적 | Microsoft 365 이상만 지원 |
Microsoft 365를 쓴다면 XLOOKUP이 현재 가장 효율적이다. =XLOOKUP(찾는값, 검색범위, 반환범위, "없을 때 표시할 값") 형태로 쓰며, 4번째 인수에 기본값을 직접 지정할 수 있어 #N/A 처리도 한 줄로 끝난다. 반면 2016 이하 버전을 혼용해야 하는 환경이라면 INDEX+MATCH가 현실적인 선택이다.
IFERROR와 IFNA — 오류 처리 함수를 올바르게 쓰는 법
오류 자체를 숨기고 싶을 때 많이 쓰는 패턴이다.
=IFERROR(VLOOKUP(A2,$B$2:$D$100,2,FALSE),"")— 오류 시 빈칸 표시=IFERROR(VLOOKUP(A2,$B$2:$D$100,2,FALSE),"없음")— 오류 시 “없음” 표시=IFNA(VLOOKUP(A2,$B$2:$D$100,2,FALSE),0)— #N/A만 잡고 나머지 오류는 원본 코드로 표시
주의할 점이 있다. IFERROR로 모든 오류를 한꺼번에 숨기면, #REF!나 #VALUE! 같은 수식 구조 문제까지 가려진다. 디버깅 단계에서는 IFERROR 없이 원본 오류 코드를 그대로 보면서 원인을 파악해야 한다. 수식이 완전히 검증된 뒤 마지막 단계에서 씌우는 것이 원칙이다. #N/A만 처리하고 나머지 오류는 보고 싶다면 IFNA가 더 적절하다.
정리 — 오류 유형별 체크리스트
- #N/A: 공백 문자(TRIM) → 숫자·텍스트 형식 통일 → 범위 첫 열 확인 → 4번째 인수 FALSE 명시
- #REF!: col_index_num이 범위 열 수를 초과하지 않는지 확인, 열 삽입·삭제 후 점검
- #VALUE!: col_index_num 인수가 유효한 양의 정수인지 확인
- 잘못된 결과(오류 없음): 절대 참조($) 여부, 중복값 존재, TRUE/FALSE 설정, 범위 정렬 상태 점검
VLOOKUP 오류는 크게 두 곳에서 온다. 데이터 품질(공백·형식)과 수식 구조(절대 참조·인수 설정)다. 오류 코드를 먼저 확인하고 위 체크리스트 순서대로 점검하면 대부분 10분 안에 원인을 찾을 수 있다. 같은 오류가 반복된다면 XLOOKUP이나 INDEX+MATCH로 전환하는 것이 장기적으로 유지 관리에 유리하다.
자주 묻는 질문
브이룩업에서 #N/A가 뜨는 가장 흔한 원인은 무엇인가요?
검색값 또는 범위 데이터에 눈에 보이지 않는 공백 문자가 섞여 있거나, 숫자와 텍스트 형식이 일치하지 않는 경우가 가장 많습니다. TRIM() 함수로 공백을 제거하고 형식을 통일한 뒤 다시 시도하세요.
VLOOKUP 4번째 인수(range_lookup)를 생략하면 어떻게 되나요?
기본값이 TRUE(근사값 검색)로 작동합니다. 검색 범위가 오름차순으로 정렬되어 있지 않으면 잘못된 값이나 #N/A가 반환됩니다. 정확한 값을 찾으려면 반드시 FALSE(또는 0)를 입력해야 합니다.
수식을 아래로 복사하면 결과가 달라지는 이유는 무엇인가요?
범위 인수에 절대 참조($)를 쓰지 않아 수식이 복사될 때 범위가 함께 밀렸기 때문입니다. =VLOOKUP(A2,$B$2:$D$100,2,FALSE)처럼 범위에 $ 기호를 붙여 고정해야 합니다.
VLOOKUP과 XLOOKUP 중 어떤 것을 써야 하나요?
Microsoft 365 이상 환경이라면 XLOOKUP을 권장합니다. 양방향 검색, 기본값 설정, 오류 처리가 한 함수에서 해결됩니다. Excel 2016 이하라면 VLOOKUP 또는 INDEX+MATCH를 써야 합니다.
IFERROR로 오류를 숨기면 문제가 없나요?
IFERROR는 모든 오류를 숨기므로 #REF!나 #VALUE! 같은 수식 구조 문제까지 가려질 수 있습니다. 디버깅 단계에서는 IFERROR 없이 원본 오류 코드를 확인한 뒤, 수식이 완전히 검증된 마지막 단계에서 씌우는 것이 좋습니다.