엑셀 VLOOKUP 함수 사용법 — #N/A부터 잡고 시작하는 실무 가이드
최종 업데이트 2026-07-20
인사팀 김대리는 사번을 입력하면 이름과 부서가 자동으로 뜨는 표를 만들려고 VLOOKUP을 넣었습니다. 그런데 결과는 값이 아니라 온통 #N/A였습니다. 수식은 분명히 맞게 쓴 것 같은데 왜 이런 일이 벌어질까요. VLOOKUP에서 가장 많이 겪는 이 상황부터 살펴보고, 원인과 해결, 그리고 정상적으로 쓰는 법까지 순서대로 정리합니다.
실패 사례 — 분명히 있는 사번인데 #N/A
| A열(사번) | B열(이름) | C열(부서) |
|---|---|---|
| A102 | 김민수 | 영업1팀 |
| A103 | 이서연 | 총무팀 |
| A104 | 박지훈 | 영업2팀 |
김대리는 F2 셀에 'A102'를 입력하고 G2에 =VLOOKUP(F2, C:A, 2, 0) 을 넣었습니다. 결과는 #N/A. C:A처럼 범위를 거꾸로 지정했거나, F2에 입력한 값이 텍스트가 아니라 공백 포함 문자열('A102 ')이었던 것이 원인이었습니다. VLOOKUP은 매우 엄격한 규칙 위에서 동작하는 함수라, 이 규칙을 모르면 데이터가 눈앞에 있어도 못 찾습니다.
VLOOKUP의 핵심 규칙 3가지
=VLOOKUP(찾을값, 찾을범위, 가져올열번호, 일치옵션)
- •찾을값은 반드시 찾을범위의 '가장 왼쪽 열'에 있어야 합니다. 오른쪽 열에 있으면 무조건 실패합니다.
- •가져올열번호는 찾을범위 안에서 몇 번째 열인지(왼쪽부터 1, 2, 3…)를 뜻하며, 시트 전체의 열 번호가 아닙니다.
- •일치옵션은 0(FALSE, 정확히 일치) 또는 1(TRUE, 근사값)입니다. 실무에서는 정렬되지 않은 데이터가 대부분이므로 거의 항상 0을 씁니다.
정상 동작 예시
위 표를 기준으로 F2에 사번을 입력하면 G2에 이름이, H2에 부서가 자동으로 나오게 하려면 각각 다음과 같이 씁니다. F2가 'A102'라면 G2는 '김민수', H2는 '영업1팀'을 반환합니다.
G2: =VLOOKUP(F2, A:C, 2, 0) H2: =VLOOKUP(F2, A:C, 3, 0)
여기서 범위 A:C의 첫 번째 열(A, 사번)에서 F2 값을 찾고, 2번째 열(B, 이름) 또는 3번째 열(C, 부서)을 가져옵니다. 열이 하나씩 밀리는 실수를 막으려면, 가져올열번호를 셀 수로 직접 세어보는 습관을 들이는 것이 좋습니다.
오류별 진단표
| 오류 | 가장 흔한 원인 | 해결 방법 |
|---|---|---|
| #N/A | 찾을값이 첫 열에 없음 / 공백·서식 불일치 | TRIM으로 공백 제거, 텍스트·숫자 형식 통일 |
| #N/A | 기준 열이 왼쪽이 아니라 오른쪽에 있음 | INDEX/MATCH 또는 XLOOKUP으로 전환 |
| #REF! | 가져올열번호가 범위의 열 수보다 큼 | 범위와 열번호를 다시 세어 맞춤 |
| #VALUE! | 가져올열번호가 1보다 작거나 소수 | 정수 1 이상의 값으로 수정 |
| 엉뚱한 값 반환 | 일치옵션을 1(근사값)로 두었는데 데이터가 미정렬 | 일치옵션을 0으로 변경 |
VLOOKUP vs INDEX/MATCH vs XLOOKUP
| 기준 | VLOOKUP | INDEX/MATCH | XLOOKUP |
|---|---|---|---|
| 왼쪽 값 조회 | 불가능 | 가능 | 가능 |
| 열 추가/삭제 안정성 | 취약(열번호 밀림) | 안전 | 안전 |
| 오류 기본값 지정 | IFERROR 필요 | IFERROR 필요 | 네 번째 인수로 바로 지정 |
| 지원 버전 | 구버전 포함 전체 | 구버전 포함 전체 | Microsoft 365, 엑셀 2021 이상 |
회사 PC가 엑셀 2019 이하 영구 라이선스라면 XLOOKUP이 없습니다. #NAME? 오류가 나면 버전부터 확인하고, 안 되면 INDEX/MATCH로 대체하세요.
정확 일치 vs 근사 일치 — 실무에서 헷갈리는 지점
일치옵션 0(정확 일치)과 1(근사값)의 차이를 모르고 쓰면 결과가 조용히 틀리는 경우가 많습니다. #N/A처럼 눈에 띄는 오류가 아니라 '있어 보이는 값'이 나오기 때문에 더 위험합니다. 예를 들어 성적을 등급으로 환산하는 표를 봅시다.
| A열(기준점수, 오름차순) | B열(등급) |
|---|---|
| 0 | F |
| 60 | D |
| 70 | C |
| 80 | B |
| 90 | A |
F2에 점수 85가 있을 때, 등급표를 근사값(1)으로 조회하면 85보다 작거나 같은 값 중 가장 큰 값인 80을 기준으로 삼아 'B'를 반환합니다. 이때 A열은 반드시 오름차순으로 정렬되어 있어야 하며, 정렬이 안 되어 있으면 결과가 무작위로 틀어집니다.
=VLOOKUP(F2, A2:B6, 2, 1)
- •정확 일치(0): 명단·코드처럼 '완전히 같은 값'만 찾아야 하는 데이터에 사용 (사번, 거래처코드 등)
- •근사 일치(1): 등급·구간표처럼 '이 값이 속하는 구간'을 찾아야 하는 데이터에 사용 (성적 등급, 세율 구간 등)
- •근사 일치를 쓸 때는 기준 열이 반드시 오름차순 정렬되어 있어야 합니다. 정렬돼 있지 않으면 결과를 신뢰할 수 없습니다.
여러 열을 한 번에 채우는 실무 팁
이름·부서·입사일처럼 여러 열을 동시에 채워야 한다면, 매번 열번호를 새로 계산하는 대신 MATCH로 열 위치를 찾아 자동화할 수 있습니다. 열 순서가 바뀌어도 수식을 고칠 필요가 없어집니다.
=VLOOKUP($F2, $A:$C, MATCH(G$1, $A$1:$C$1, 0), 0)
G1에 '이름', H1에 '부서'처럼 가져올 항목명을 헤더로 적어두면, MATCH가 그 항목명이 원본 표에서 몇 번째 열인지 자동으로 찾아줍니다. 원본 표에 열이 추가되거나 순서가 바뀌어도 열번호를 하나하나 수정할 필요가 없습니다.
기준 열이 오른쪽에 있어 VLOOKUP이 안 될 때는 INDEX/MATCH 조합을 쓰면 방향과 상관없이 값을 가져올 수 있습니다. 매일 쓰는 함수라면 이 글의 INDEX/MATCH 가이드도 함께 보시길 권합니다.