VLOOKUP은 엑셀에서 가장 많이 쓰이면서 가장 자주 실패하는 함수입니다. 실패 원인은 거의 정해져 있고, 아래 예제 표 하나로 전부 재현해 볼 수 있습니다.
💡 핵심 —
=VLOOKUP(찾을값, 범위, 열번호, FALSE). 코드·사번·이름처럼 “딱 그 값”을 찾을 때는 네 번째 인수를 FALSE(또는 0) 로 씁니다. 비워 두면 TRUE(근사 일치)로 동작해, 정렬되지 않은 표에서는 엉뚱한 값을 오류 없이 돌려줍니다. TRUE는 점수→등급처럼 구간을 찾을 때만 씁니다.
예제 표 — 복사해서 따라 하기
아래 표를 드래그해 복사한 뒤 빈 시트의 A1에 붙여넣으세요. 머리글이 1행, 데이터가 2~6행에 들어가야 이 글의 수식이 그대로 맞습니다.
| 상품코드 | 상품명 | 단가 | 재고 |
|---|---|---|---|
| P-001 | 노트북 | 1250000 | 12 |
| P-002 | 모니터 | 380000 | 30 |
| P-003 | 키보드 | 45000 | 0 |
| P-004 | 마우스 | 25000 | 85 |
| P-005 | 웹캠 | 72000 | 7 |
이제 F2에 P-003을 입력하고, G2부터 아래 수식을 하나씩 넣어 보세요.
| 수식 | 결과 | 설명 |
|---|---|---|
=VLOOKUP(F2, $A$2:$D$6, 2, FALSE) |
키보드 | A열에서 P-003을 찾아 2번째 열(B) 값 |
=VLOOKUP(F2, $A$2:$D$6, 3, FALSE) |
45000 | 3번째 열(C) 값 |
=VLOOKUP("P-009", $A$2:$D$6, 2, FALSE) |
#N/A | 표에 없는 코드 |
=VLOOKUP(F2, $A$2:$D$6, 5, FALSE) |
#REF! | 범위는 4열인데 5번째 열을 요구 |
=VLOOKUP(F2, $A$2:$D$6, 0, FALSE) |
#VALUE! | 열번호는 1 이상 |
=VLOOKUP("노트북", $A$2:$D$6, 3, FALSE) |
#N/A | 상품명은 첫 열이 아니라서 못 찾음 |
=VLOOKUP("p-003", $A$2:$D$6, 2, FALSE) |
키보드 | 대소문자는 구분하지 않음 |
=VLOOKUP("*보드", $A$2:$D$6, 2, FALSE) |
키보드 | FALSE일 때는 *·? 와일드카드 사용 가능 |
인수 네 개의 뜻
=VLOOKUP(F2, $A$2:$D$6, 3, FALSE)
↑ ↑ ↑ ↑
찾을값 범위 열번호 일치방식| 인수 | 뜻 | 주의 |
|---|---|---|
| 찾을값 | 무엇을 찾을지 | 범위의 첫 열과 같은 자료형이어야 함 |
| 범위 | 어디서 찾을지 | 첫 열에서만 찾고, 절대참조($)로 고정 |
| 열번호 | 범위 안에서 몇 번째 열을 가져올지 | 시트 열 번호가 아니라 범위의 왼쪽부터 1 |
| 일치방식 | FALSE=정확히, TRUE=근사 | 생략하면 TRUE |
열번호는 시트 전체가 아니라 지정한 범위 안에서 셉니다. 범위가 $C$2:$D$6이면 C가 1, D가 2입니다. 위 표에서 =VLOOKUP("키보드", $B$2:$D$6, 2, FALSE)는 범위를 B열부터 잡았기 때문에 첫 열이 상품명이 되어 45000을 돌려줍니다.
같은 값이 여러 행에 있으면 위에서 첫 번째로 만난 행의 값만 가져옵니다. 두 번째 이후 행은 VLOOKUP으로 가져올 수 없습니다.
정확 일치(FALSE)와 근사 일치(TRUE)의 차이
| FALSE / 0 | TRUE / 1 / 생략 | |
|---|---|---|
| 찾는 방식 | 똑같은 값만 | 찾을값 이하인 값 중 가장 큰 값 |
| 정렬 | 필요 없음 | 첫 열이 오름차순이어야 함 |
| 없을 때 | #N/A | 가장 가까운 아래 구간을 반환(가장 작은 값보다 작으면 #N/A) |
| 와일드카드 | 가능 | 불가 |
| 용도 | 코드·사번·이름 조회 | 점수→등급, 금액→구간 요율 |
TRUE가 위험한 이유는 틀려도 오류가 안 난다는 점입니다. 정렬되지 않은 코드 표에서 TRUE(또는 생략)로 찾으면 이진 검색이 중간에 멈춰, 다른 행의 값을 멀쩡한 결과처럼 돌려줍니다. 코드 조회에서 “값은 나오는데 다른 상품 값이 나온다”면 가장 먼저 네 번째 인수를 확인하세요.
TRUE가 맞는 경우 — 등급표
I1부터 아래 표를 붙여넣으세요(I열 점수, J열 등급). 첫 열이 오름차순이어야 합니다.
| 점수이상 | 등급 |
|---|---|
| 0 | F |
| 60 | D |
| 70 | C |
| 80 | B |
| 90 | A |
| 수식 | 결과 |
|---|---|
=VLOOKUP(85, $I$2:$J$6, 2, TRUE) |
B |
=VLOOKUP(59, $I$2:$J$6, 2, TRUE) |
F |
=VLOOKUP(100, $I$2:$J$6, 2, TRUE) |
A |
=VLOOKUP(-5, $I$2:$J$6, 2, TRUE) |
#N/A |
=VLOOKUP(85, $I$2:$J$6, 2, FALSE) |
#N/A |
마지막 줄처럼 등급표에 FALSE를 쓰면 85라는 값이 그대로 없어서 #N/A가 납니다. 구간 조회는 TRUE, 값 조회는 FALSE입니다.
#N/A가 뜨는 다섯 가지 원인
1. 눈에 안 보이는 공백
웹이나 시스템에서 복사한 값에는 앞뒤 공백이 붙어 있는 경우가 많습니다. "P-003 "과 "P-003"은 다른 값입니다. 먼저 길이를 비교해 보세요.
| 수식 | 뜻 |
|---|---|
=LEN(F2) |
5가 아니라 6이 나오면 공백이 숨어 있음 |
=F2=A4 |
눈에는 같아 보이는데 FALSE면 어딘가 다름 |
=VLOOKUP(TRIM(F2), $A$2:$D$6, 2, FALSE)TRIM은 일반 공백만 지웁니다. 웹페이지에서 복사한 값에 흔한 줄바꿈 없는 공백(CHAR(160)) 은 TRIM으로 안 지워지므로 함께 처리합니다.
=VLOOKUP(TRIM(SUBSTITUTE(F2, CHAR(160), "")), $A$2:$D$6, 2, FALSE)2. 숫자인데 텍스트인 것
사번 1001이 한쪽은 숫자, 다른 쪽은 텍스트면 VLOOKUP은 다른 값으로 봅니다. 셀 왼쪽 위에 초록 삼각형이 있거나, 숫자가 왼쪽 정렬돼 있으면 텍스트입니다. =ISNUMBER(F2)와 =ISNUMBER(A2) 결과가 다르면 이 경우입니다.
| 상황 | 수식 |
|---|---|
| 찾을값이 텍스트, 표가 숫자 | =VLOOKUP(VALUE(F2), $A$2:$D$6, 2, FALSE) 또는 F2*1 |
| 찾을값이 숫자, 표가 텍스트 | =VLOOKUP(F2 & "", $A$2:$D$6, 2, FALSE) |
| 어느 쪽인지 모름 | 표 쪽 열을 선택 → 데이터 → 텍스트 나누기 → 마침으로 숫자화 |
3. 범위에 $를 안 붙임
수식을 아래로 끌면 범위도 같이 내려가 아래쪽 데이터가 범위 밖으로 나갑니다. 위쪽 몇 행은 맞고 아래로 갈수록 #N/A가 늘어나는 패턴이면 이 원인입니다.
$A$2:$D$6 ← 이렇게
A2:D6 ← 이러면 끌 때 어긋납니다수식 입력 중 범위를 선택하고 F4를 누르면 $가 붙습니다.
4. 찾을 값이 범위의 첫 열에 없음
VLOOKUP은 범위의 첫 열에서만 찾습니다. 위 표에서 상품명으로 상품코드를 찾으려면 $A$2:$D$6을 아무리 지정해도 안 됩니다. 범위를 그 열부터 시작하도록 다시 잡거나, 아래 “왼쪽 값을 가져와야 할 때”의 방법을 씁니다.
5. 진짜로 없음
위 넷을 다 확인했는데도 안 나오면 정말 없는 값입니다. 이때는 오류 대신 안내 문구를 띄우되, IFNA와 IFERROR 중 무엇을 쓸지가 중요합니다.
IFNA와 IFERROR의 차이
| IFNA | IFERROR | |
|---|---|---|
| 잡는 오류 | #N/A만 | #N/A, #REF!, #VALUE!, #DIV/0!, #NAME? 전부 |
| 열번호 오류(#REF!) | 그대로 보임 | “없음”으로 가려짐 |
| 지원 버전 | 엑셀 2013 이상, 구글 시트 | 엑셀 2007 이상, 구글 시트 |
=IFNA(VLOOKUP(F2, $A$2:$D$6, 2, FALSE), "미등록")
=IFERROR(VLOOKUP(F2, $A$2:$D$6, 2, FALSE), "미등록")F2가 P-009면 둘 다 미등록을 표시합니다. 그런데 열번호를 5로 잘못 쓴 경우를 비교해 보세요.
| 수식 | 결과 |
|---|---|
=IFNA(VLOOKUP(F2, $A$2:$D$6, 5, FALSE), "미등록") |
#REF! |
=IFERROR(VLOOKUP(F2, $A$2:$D$6, 5, FALSE), "미등록") |
미등록 |
IFERROR는 수식 자체의 실수까지 “미등록”으로 덮어 버려 표 전체가 미등록으로 보일 때까지 눈치채기 어렵습니다. “값이 없을 때”만 처리하려면 IFNA가 맞고, IFERROR는 나눗셈 오류처럼 여러 종류의 오류를 한 번에 가려야 할 때 씁니다.
왼쪽 값을 가져와야 할 때
VLOOKUP은 오른쪽만 볼 수 있습니다. 상품명 “키보드”로 왼쪽의 상품코드를 가져오려면 다른 함수를 씁니다.
XLOOKUP — 엑셀 2021·Microsoft 365·구글 시트에서 쓸 수 있습니다. 방향 제한이 없고 못 찾을 때 값을 네 번째 인수로 바로 줍니다.
=XLOOKUP("키보드", $B$2:$B$6, $A$2:$A$6, "없음")
찾을값 찾을범위 가져올범위 못 찾을 때결과: P-003
INDEX + MATCH — 구버전에서도 됩니다.
=INDEX($A$2:$A$6, MATCH("키보드", $B$2:$B$6, 0))결과: P-003. MATCH의 마지막 0이 VLOOKUP의 FALSE와 같은 역할입니다.
열을 추가해도 안 깨지게
열번호 3을 숫자로 적어 두면 범위 가운데에 열을 끼워 넣는 순간 다른 열을 가져옵니다. 머리글에서 열 이름을 찾아 번호를 계산하게 하면 이 문제가 없어집니다.
=VLOOKUP(F2, $A$2:$D$6, MATCH("단가", $A$1:$D$1, 0), FALSE)결과: 45000. 열 순서가 바뀌어도 “단가” 열을 따라갑니다.
그 밖의 오류
| 표시 | 원인 |
|---|---|
| #REF! | 열번호가 범위의 열 수보다 큼 |
| #VALUE! | 열번호가 1보다 작음, 또는 찾을값이 255자를 넘음 |
| #NAME? | 함수명 오타, 또는 텍스트 찾을값에 큰따옴표 누락(P-003 → "P-003") |
| 값은 나오는데 틀림 | 네 번째 인수 생략·TRUE, 또는 열번호를 시트 열 기준으로 셈 |
인수 구분자는 보통 쉼표(,)지만, 시스템 지역 설정에 따라 세미콜론(;)을 써야 하는 환경도 있습니다. 쉼표를 넣었는데 수식 오류 안내가 뜨면 세미콜론으로 바꿔 보세요.
점검 순서
- 네 번째 인수가 FALSE인가 (값 조회라면)
- 범위에 $ 가 붙어 있는가
- 찾을값이 범위의 첫 열에 있는가
=LEN()·=ISNUMBER()로 공백·자료형이 양쪽 같은가- 그래도 없으면 IFNA로 “없음” 처리
함수 인수와 반환값의 공식 정의는 Microsoft 지원 문서 VLOOKUP 함수에서 확인할 수 있습니다. 다른 함수의 구문과 예시는 엑셀 함수 사전에서 검색해 복사할 수 있습니다.