엑셀 VLOOKUP 함수 사용법 — 예제로 이해하는 정확히 일치와 근사 일치
이 글 핵심 요약
- VLOOKUP은 표의 첫 번째 열에서 값을 찾아, 같은 행의 다른 열 값을 가져온다
- 코드·이름처럼 딱 맞는 값을 찾을 때는 마지막 인수에 FALSE를 넣는다
- 마지막 인수를 비우면 근사 일치(TRUE)로 동작하므로 주의한다
- 찾는 값이 없으면 #N/A가 나오고, IFERROR로 원하는 문구로 바꿀 수 있다
거래처 목록에서 코드로 회사명을 찾거나, 상품표에서 상품 코드로 단가를 가져올 때 쓰는 함수가 VLOOKUP입니다. 한 번 익혀 두면 표 두 개를 맞춰 보는 작업을 손으로 찾지 않고 끝낼 수 있습니다.
이 글은 Microsoft 공식 도움말을 기준으로, 실제로 따라 할 수 있는 예제 표와 함께 설명합니다.
VLOOKUP이 하는 일
VLOOKUP은 표의 맨 왼쪽 열에서 값을 찾은 뒤, 같은 행에서 원하는 열의 값을 돌려주는 함수입니다. Microsoft 도움말의 표현으로는 "테이블이나 범위에서 행별로 항목을 찾아야 할 때" 쓰는 함수입니다.
예: P-103
같은 값을 찾음
N번째 열 값을 반환
함수 구조: 인수 4개
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| 인수 | 뜻 | 꼭 넣어야 하나 |
|---|---|---|
| lookup_value | 찾으려는 값. 셀 주소나 값을 직접 넣을 수 있습니다 | 필수 |
| table_array | 찾을 범위. 찾을 값이 이 범위의 첫 번째 열에 있어야 합니다 | 필수 |
| col_index_num | 가져올 값이 범위의 왼쪽에서 몇 번째 열인지 (첫 열 = 1) | 필수 |
| range_lookup | FALSE(또는 0) = 정확히 일치, TRUE(또는 1) = 근사 일치. 비우면 TRUE | 선택 |
특히 주의할 것은 네 번째 인수입니다. 비워 두면 근사 일치로 동작해서, 첫 번째 열이 정렬되지 않은 표에서는 잘못된 값이 나올 수 있습니다. 코드나 이름을 찾을 때는 FALSE를 꼭 넣으세요.
예제 1. 상품 코드로 단가 찾기 (정확히 일치)
아래와 같은 상품표가 있다고 해 보겠습니다.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 코드 | 상품명 | 단가 | 찾을 코드 | 단가 | |
| 2 | P-101 | 볼펜 | 800 | P-103 | 1200 | |
| 3 | P-102 | 노트 | 2500 | |||
| 4 | P-103 | 형광펜 | 1200 | |||
| 5 | P-104 | 파일 | 3000 | |||
| 6 | P-105 | 지우개 | 500 |
F2 셀에 이렇게 입력합니다.
=VLOOKUP(E2, $A$2:$C$6, 3, FALSE)
E2: 찾을 값(P-103)이 들어 있는 셀$A$2:$C$6: 찾을 범위. 코드가 있는 A열이 첫 번째 열입니다3: 단가는 범위의 세 번째 열(C열)FALSE: 코드가 정확히 같은 행만 찾기
결과는 1200입니다. 열 번호를 2로 바꾸면 상품명인 형광펜이 나옵니다. E2에 P-105를 넣으면 500이 나옵니다.
범위에
$를 붙인 이유: 수식을 아래로 복사해도 찾을 범위가 움직이지 않게 고정하기 위해서입니다(절대 참조). 수식 입력줄에서 셀 주소를 선택하고 F4 키를 누르면$가 붙는 참조 형식이 차례로 바뀌므로,$A$2처럼 행과 열 모두에$가 붙을 때 멈추면 됩니다.
예제 2. 점수로 등급 매기기 (근사 일치)
근사 일치(TRUE)는 "구간"으로 값을 정할 때 씁니다. 예를 들어 점수 구간별 등급표가 있다면:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 기준 점수 | 등급 | 점수 | 등급 | |
| 2 | 0 | F | 95 | A | |
| 3 | 60 | D | 83 | B | |
| 4 | 70 | C | 70 | C | |
| 5 | 80 | B | 59 | F | |
| 6 | 90 | A |
E2 셀에 입력하고 E5까지 복사합니다.
=VLOOKUP(D2, $A$2:$B$6, 2, TRUE)
근사 일치는 같은 값이 있으면 그 값을, 없으면 찾는 값보다 작은 값 중 가장 큰 값을 찾습니다(Microsoft Learn의 VLookup 설명). 70점은 기준 70과 같으므로 C, 83점은 80 이상 90 미만이므로 기준 80의 B, 59점은 기준 0의 F가 됩니다.
근사 일치를 쓸 때 지켜야 할 조건이 두 가지 있습니다.
- 첫 번째 열을 오름차순으로 정렬해야 합니다. 정렬되어 있지 않으면 잘못된 값이 나올 수 있습니다.
- 찾는 값이 첫 번째 열의 가장 작은 값보다 작으면 #N/A가 나옵니다. 그래서 위 표는 0부터 시작합니다.
예제 3. 없는 값을 찾을 때: #N/A와 IFERROR
예제 1의 표에서 없는 코드 P-109를 찾으면 결과는 #N/A입니다. 정확히 일치(FALSE)에서 #N/A는 "같은 값을 찾지 못했다"는 뜻입니다.
오류 대신 원하는 문구를 보여 주려면 IFERROR 함수로 감쌉니다(IFERROR 함수 사용법).
=IFERROR(VLOOKUP(E2, $A$2:$C$6, 3, FALSE), "없는 코드")
IFERROR(값, 오류일 때 보여 줄 값)는 첫 번째 인수가 오류이면 두 번째 인수를, 아니면 원래 결과를 돌려줍니다. 위 수식에서 P-109를 찾으면 없는 코드, P-103을 찾으면 그대로 1200이 나옵니다.
자주 나는 오류와 해결법
| 오류 | 원인 | 해결 |
|---|---|---|
| #N/A | 정확히 일치(FALSE)에서 같은 값이 없음 | 찾는 값 철자, 앞뒤 공백 확인. 필요하면 IFERROR로 처리 |
| #N/A | 근사 일치(TRUE)에서 찾는 값이 첫 열의 최솟값보다 작음 | 기준표 첫 행을 가장 작은 값(예: 0)으로 시작 |
| 엉뚱한 값 | 네 번째 인수를 비워 근사 일치로 동작했는데 첫 열이 정렬 안 됨 | 정확히 일치가 목적이면 FALSE 입력 |
| #REF! | 열 번호가 범위의 열 수보다 큼 (예: 3열짜리 범위에 4 입력) | 열 번호를 범위 안의 숫자로 수정 |
| #NAME? | 텍스트를 따옴표 없이 입력 | "P-103"처럼 큰따옴표로 감싸기 |
값이 분명히 있는데 결과가 이상할 때
Microsoft 도움말은 아래 두 경우 VLOOKUP이 부정확하거나 예기치 않은 값을 돌려줄 수 있다고 안내합니다.
- 숫자·날짜가 텍스트로 저장된 경우: 첫 번째 열의 숫자나 날짜가 텍스트 형식으로 저장되어 있지 않은지 확인하세요.
- 앞뒤 공백이나 인쇄할 수 없는 문자가 섞인 경우: 첫 번째 열의 텍스트에 공백이나 보이지 않는 문자가 붙어 있으면 결과가 틀어질 수 있습니다. TRIM 함수나 CLEAN 함수로 정리한 뒤 다시 찾습니다(TRIM·CLEAN 함수 사용법).
VLOOKUP의 한계
- 찾을 값이 반드시 범위의 첫 번째 열에 있어야 합니다. 가져올 값이 찾을 값보다 왼쪽에 있으면 VLOOKUP으로는 찾을 수 없습니다.
- 이럴 때는 어느 방향으로나 찾을 수 있는 XLOOKUP 함수를 쓸 수 있습니다. Microsoft도 XLOOKUP을 VLOOKUP의 개선된 버전으로 안내합니다. XLOOKUP의 사용법과 지원 버전은 Microsoft의 XLOOKUP 도움말에서 확인하세요.
자주 묻는 질문
네 번째 인수에 FALSE 대신 0을 써도 되나요?
됩니다. Microsoft 도움말에 따르면 정확히 일치는 0 또는 FALSE, 근사 일치는 1 또는 TRUE로 지정합니다.
다른 시트의 표에서도 찾을 수 있나요?
됩니다. 범위 앞에 시트 이름과 느낌표를 붙입니다. Microsoft 도움말의 예시는 =VLOOKUP(A2,'Client Details'!A:F,3,FALSE)로, 띄어쓰기가 있는 시트 이름을 작은따옴표로 감싸서 썼습니다. 같은 방식으로 =VLOOKUP(A2, '상품 목록'!A:C, 3, FALSE)처럼 쓸 수 있습니다.
일부만 일치하는 값도 찾을 수 있나요?
정확히 일치(FALSE)이고 찾는 값이 텍스트라면 와일드카드를 쓸 수 있습니다. ?는 한 글자, *는 글자 수에 상관없는 임의의 문자열을 뜻합니다. 예를 들어 "P-10?"은 "P-10" 뒤에 한 글자가 붙은 코드를 찾습니다. 물음표나 별표 자체를 찾으려면 앞에 물결표(~)를 붙입니다.
참고한 자료
- Microsoft 지원 — VLOOKUP 함수
- Microsoft Learn — WorksheetFunction.VLookup (근사 일치 규칙)
- Microsoft 지원 — IFERROR 함수
- Microsoft 지원 — 상대 참조, 절대 참조 및 혼합 참조 간 전환
예제의 결과값은 Microsoft 도움말에 설명된 VLOOKUP 규칙에 따라 계산해 확인했습니다.