VLOOKUP #N/A 오류 완벽 가이드 — 원인 10가지와 해결(공백/형식/정렬/IFNA)
VLOOKUP #N/A 오류는 “찾을 값을 테이블에서 못 찾았다”는 뜻입니다. 대부분 공백/숨은 문자, 숫자↔텍스트 형식 불일치, 근사일치에서 정렬 전제 위반 같은 데이터 품질 문제로 발생합니다. 아래 체크리스트대로 순서 점검하면 대부분 5분 내 해결됩니다. (Microsoft 가이드 참조)
Quick Fix (3분)
- 정확일치인지 확인:
=VLOOKUP(키,표,열,0)에서 네 번째 인수는0/FALSE. - 공백/숨은 문자 제거:
=TRIM(셀)로 양쪽/중복 공백 제거,=CLEAN(셀)로 비인쇄 문자 제거 → 값만 붙여넣기. - 숫자↔텍스트 리셋: 열 서식을 동일화(일반/숫자)하고 데이터 > 텍스트 나누기(마침)로 강제 재해석.
- #N/A 표시 다루기: 근본 해결 전 임시로
=IFNA(VLOOKUP(...),"값없음")또는IFERROR로 사용자 메시지 표시.
#N/A가 나는 10가지 원인과 해결
1) 원본에 정말 값이 없음
오타/누락/범위 선택 오류. 먼저 찾을 값이 원본에 존재하는지 필터/찾기(Ctrl+F)로 확인하세요. 없으면 #N/A가 정상 동작입니다.
2) 앞/뒤/중간 공백으로 다른 값 취급
=TRIM(A2)로 앞뒤 및 중복 공백 제거(단어 사이 하나는 유지).=CLEAN(A2)로 비인쇄 문자(개행, 제어문자) 제거 → TRIM과 함께 쓰면 튼튼합니다.- 함수 적용 후 값만 붙여넣기로 원본 교체.
TRIM/CLEAN은 Microsoft가 권장하는 텍스트 정리 1순위 도구입니다.
3) 숫자를 텍스트로 인식하거나 그 반대
- 셀 서식을 동일화(일반/숫자)하고, 데이터 > 텍스트 나누기(마침)로 열을 재구문 분석하면 형식 엇갈림이 사라집니다.
- 수식으로는
=VALUE(A2)로 텍스트 숫자를 숫자로 강제 변환 가능.
Microsoft의 “#N/A 고치기” 문서에서도 형식 일치를 핵심 해결로 안내합니다.
4) 근사일치(TRUE/1)에서 정렬 전제 위반
근사일치 모드는 첫 열 오름차순 정렬이 전제입니다. 정렬이 깨져 있으면 #N/A 또는 오답. 모호하면 정확일치(0)로 사용하세요.
5) 숨은 제어문자·웹 복사 흔적
웹/ERP에서 복사된 값에는 보이지 않는 LF/CR 등이 섞일 수 있습니다. CLEAN으로 제거 후 TRIM을 중첩하세요.
6) 더블바이트(전각) 문자/다른 문자셋
제품코드에 전각 공백/문자가 끼면 일치가 실패합니다. 데이터 정리(대/소문자·문자폭 통일) 가이드를 참고하세요.
7) 키 열이 첫 번째 열이 아님
VLOOKUP은 테이블의 첫 열에서만 키를 찾습니다. 키가 오른쪽에 있으면 INDEX+MATCH 또는 XLOOKUP으로 대체하세요.
8) 숨은 공백이 리스트 전체에 반복
대량 정리는 찾기/바꾸기(Ctrl+H)에서 공백→빈칸 치환 또는 Power Query “변환 > 공백 제거”가 빠릅니다.
9) 범위가 늘어날 때 참조가 밀림
테이블(CTRL+T)로 범위를 고정하면 신규 행이 자동 포함됩니다. 구조참조는 유지보수에 안전합니다.
10) 오탈자/일부만 일치
부분 일치라면 정확일치 VLOOKUP 대신 XLOOKUP의 와일드카드(*,?) 사용 또는 보조열로 표준화하세요.
실무 예제(재현 가능)
| 상황 | 원인 | 해결 |
|---|---|---|
| 같은 숫자인데 한쪽만 #N/A | 텍스트 vs 숫자 | 서식 통일, 텍스트 나누기(마침)로 재해석, VALUE() |
| 값이 보이는데도 #N/A | 뒤 공백/제어문자 | TRIM(CLEAN(셀)) → 값 붙여넣기 |
| TRUE/1 모드에서 엉뚱한 값 | 정렬 전제 위반 | 첫 열 오름차순 정렬 또는 정확일치(0)로 변경 |
| 키가 오른쪽 열 | VLOOKUP 제약 | INDEX(MATCH()) 또는 XLOOKUP |
| 일부만 일치 | 접두/접미 문자 | 보조열로 표준화, TRIM/SUBSTITUTE |
#N/A는 감추되 “기록”하자 (IFNA/IFERROR)
보고서에서는 에러를 숨기되, 품질 개선을 위해 로그는 남기세요.
=IFNA(VLOOKUP($G10,$B$3:$E$6,4,0),"값없음")Exceljet은 IFNA로 #N/A만 잡거나, IFERROR로 광범위 에러를 포착하는 패턴을 권장합니다.
클린업 레시피(복사해 쓰기)
- 공백/제어문자 동시 제거:
=TRIM(CLEAN(A2)) - 텍스트 숫자 → 숫자:
=VALUE(A2)또는 서식 통일 후 텍스트 나누기(마침) - 정렬 전제 확인: 근사일치(1/TRUE) 사용 시 첫 열 오름차순 정렬
- 대체 함수:
=XLOOKUP(키,범위,반환, "없음", 0)또는INDEX+MATCH
트러블슈팅 표
| 증상 | 의심 구간 | 점검/처치 |
|---|---|---|
| #N/A | 키/범위 | 정확일치 인수 0, 키 존재 여부, 첫 열 확인 |
| 간헐적 #N/A | 형식 | 일반/숫자 통일, TEXT TO COLUMNS 리셋 |
| 줄 바꾼 티가 안 남는데 실패 | 숨은 문자 | CLEAN 후 TRIM |
| 근사 모드 오답 | 정렬 | 첫 열 오름차순 재정렬 또는 정확일치 |
| 새 행 추가 후 실패 | 범위 | 테이블(CTRL+T)·구조참조로 전환 |
관련 글
참고: Microsoft — #N/A 오류 수정, TRIM/CLEAN 및 데이터 정리 가이드. Exceljet — IFNA/IFERROR 패턴.