VLOOKUP 사용법 — 인수 4개와 정확히 일치·대략 일치 차이 예제로 정리
=VLOOKUP(찾는값, 표 범위, 열 번호, FALSE)를 씁니다. 마지막 인수를 FALSE로 두면 정확히 같은 값만 찾고, 생략하거나 TRUE로 두면 대략 일치(정렬된 표 전제)로 동작합니다.VLOOKUP의 구조: 인수 4개
VLOOKUP은 표의 맨 왼쪽 열에서 값을 찾은 뒤, 그 행에서 원하는 열의 값을 돌려주는 함수입니다. 마이크로소프트 문서에 나온 형식은 다음과 같습니다.
=VLOOKUP(찾는값, 표 범위, 열 번호, [일치 유형])| 인수 | 뜻 | 주의 |
|---|---|---|
| 찾는값 | 찾으려는 값 | 표 범위의 첫 번째 열에 있어야 합니다. |
| 표 범위 | 찾는 열과 가져올 열을 모두 포함한 범위 | 첫 열이 찾는 열이어야 합니다. |
| 열 번호 | 돌려받을 값이 표 범위의 몇 번째 열인지 | 시트의 열 번호가 아니라 범위 안에서 1부터 센 번호입니다. |
| 일치 유형 | FALSE(또는 0) = 정확히 일치TRUE(또는 1) = 대략 일치 | 생략하면 TRUE(대략 일치)로 동작합니다. |
정확히 일치로 찾기 (FALSE)
아래 표가 A1:C5에 있다고 가정합니다.
| A | B | C | |
|---|---|---|---|
| 1 | 코드 | 품명 | 단가 |
| 2 | P001 | 연필 | 500 |
| 3 | P002 | 노트 | 1500 |
| 4 | P003 | 지우개 | 300 |
| 5 | P004 | 가위 | 2500 |
코드 P003의 품명과 단가를 가져오는 수식입니다. 단가는 범위 A2:C5 안에서 세 번째 열이므로 열 번호가 3입니다.
| 가져올 값 | 수식 | 결과 |
|---|---|---|
| 품명 | =VLOOKUP("P003",A2:C5,2,FALSE) | 지우개 |
| 단가 | =VLOOKUP("P003",A2:C5,3,FALSE) | 300 |
| 없는 코드 | =VLOOKUP("P009",A2:C5,3,FALSE) | #N/A |
글자를 수식에 직접 쓸 때는 큰따옴표로 감싸야 합니다. 따옴표를 빼면 엑셀이 그 글자를 이름이나 셀 참조로 해석하며, 정의되지 않은 이름이면 보통 #NAME? 오류가 납니다. 실무에서는 코드를 다른 셀(예: E2)에 입력해 두고 =VLOOKUP(E2,A2:C5,3,FALSE)처럼 셀을 가리키게 하는 편이 편합니다.
정확히 일치 모드에서는 텍스트에 한해 와일드카드도 쓸 수 있습니다. ?는 한 글자, *는 0개 이상의 문자를 뜻합니다. 예를 들어 =VLOOKUP("연*",B2:C5,2,FALSE)는 품명이 「연」으로 시작하는 첫 행(연필)의 단가 500을 돌려줍니다. 이때는 찾는 열이 B열이므로 범위를 B2:C5로 잡았습니다.
아래로 복사할 때 범위 고정하기
수식을 아래 칸으로 복사하면 셀 참조가 한 칸씩 따라 내려갑니다. 표 범위가 같이 밀리면 값을 찾지 못하거나 잘못된 값을 돌려줄 수 있습니다. 그래서 표 범위는 절대 참조($)로 고정합니다. 마이크로소프트 문서도 같은 이유로 절대 참조 사용을 권합니다.
=VLOOKUP(E2,$A$2:$C$5,3,FALSE)수식을 편집하는 중에 수식 입력줄에서 A2:C5 참조를 선택한 뒤 F4 키를 누르면 누를 때마다 참조 형식이 A2:C5 → $A$2:$C$5(절대 참조) → A$2:C$5(행만 고정) → $A2:$C5(열만 고정) → A2:C5 순으로 돌아가며 바뀝니다. VLOOKUP의 표 범위는 보통 두 번째 모양인 $A$2:$C$5를 씁니다. 일부 노트북은 Fn 키를 함께 눌러야 할 수 있고, Mac에서는 Command+T를 쓰거나 환경에 따라 F4를 씁니다.
대략 일치로 구간 찾기 (TRUE)
점수 구간별 등급처럼 기준값 이상 구간을 찾을 때는 대략 일치를 씁니다. 이 모드는 첫 번째 열이 오름차순으로 정렬되어 있다고 가정하고, 찾는값보다 크지 않은 가장 가까운 값의 행을 돌려줍니다. 정렬이 안 되어 있으면 엉뚱한 값이 나올 수 있습니다.
G1:H6에 아래 기준표가 있다고 합니다. 머리글은 G1:H1, 실제 데이터는 G2:H6이며 구간의 시작 점수가 오름차순입니다.
| 점수(이상) | 등급 |
|---|---|
| 0 | F |
| 60 | D |
| 70 | C |
| 80 | B |
| 90 | A |
| 점수 | 수식 | 결과 |
|---|---|---|
| 85 | =VLOOKUP(85,$G$2:$H$6,2,TRUE) | B |
| 90 | =VLOOKUP(90,$G$2:$H$6,2,TRUE) | A |
| 59 | =VLOOKUP(59,$G$2:$H$6,2,TRUE) | F |
| 79 | =VLOOKUP(79,$G$2:$H$6,2,TRUE) | C |
표의 가장 작은 값(여기서는 0)보다 작은 값을 찾으면 #N/A가 납니다. 그래서 기준표 맨 위를 0으로 시작하게 만든 것입니다.
FALSE를 안 쓰면 정렬되지 않은 표에서 엉뚱한 행을 가져올 수 있습니다.VLOOKUP이 못 하는 것
- 찾는 열이 가져올 열보다 오른쪽이면 안 됩니다. 찾는 값은 항상 표 범위의 첫 열이어야 하고, 값은 표 범위의 첫 열 자체 또는 그 오른쪽 열에서만 가져올 수 있습니다. 왼쪽 열 값이 필요하면 열 순서를 바꾸거나 다른 함수를 써야 합니다.
- 열 번호를 직접 세야 합니다. 표 중간에 열을 끼워 넣으면 열 번호가 어긋날 수 있습니다.
마이크로소프트 문서는 이런 제약이 적은 XLOOKUP 사용을 권장합니다. 다만 XLOOKUP은 오래된 엑셀 버전에서는 쓸 수 없으니, 파일을 주고받는 상대의 버전을 확인하세요.
자주 나는 오류
| 오류 | 원인 | 해결 |
|---|---|---|
#N/A | 정확히 일치에서 값을 못 찾음, 또는 대략 일치에서 찾는값이 첫 열의 최솟값보다 작음 | 철자·공백을 확인합니다. 표 첫 열에 값이 정말 있는지 봅니다. |
#REF! | 열 번호가 표 범위의 열 수보다 큼 | 범위가 A2:C5(3열)이면 열 번호는 3까지만 쓸 수 있습니다. |
#VALUE! | 열 번호가 1보다 작거나 숫자가 아님, 또는 찾는값이 255자를 넘음 | 열 번호를 1 이상의 숫자로 고칩니다. 찾는값이 너무 길면 줄이거나 INDEX·MATCH를 씁니다. |
#NAME? | 글자 값에 큰따옴표를 안 붙여 정의되지 않은 이름으로 해석됨 | "P003"처럼 감쌉니다. |
눈으로 같아 보이는데 못 찾는 경우도 많습니다. 마이크로소프트 문서는 찾는 열에 숫자나 날짜를 텍스트로 저장하지 말 것, 앞뒤 공백이나 이상한 문자가 있으면 TRIM·CLEAN 함수로 정리할 것을 권합니다. 다만 TRIM은 주로 일반 공백을, CLEAN은 일부 비인쇄 문자를 지우므로, 줄 바꿈 없는 공백 같은 특수 문자는 SUBSTITUTE 등으로 따로 바꿔야 할 수 있습니다. 찾는값은 숫자인데 표 쪽은 글자(또는 그 반대)인 경우도 같은 증상이 납니다.