VLOOKUP 함수 완벽 가이드 — 정확/근사/왼쪽조회/마지막값/오류 해결
VLOOKUP 함수는 표의 첫 번째 열에서 값을 찾고, 오른쪽에 있는 열의 결과를 반환하는 세로 조회 함수입니다. 이 글은 초보부터 실무까지 한 번에 끝내도록 Quick Fix→개념→예제→오류 해결→최적화 순으로 구성했습니다.
다중 조건이 필요하면: 다중 조건 VLOOKUP에서 바로 이어가세요.
Quick Fix (3분 설치): 정확일치로 당장 결과 보기
- 표 범위 지정: A1:D100처럼
table_array범위를 선택합니다. 조회키(찾을 값)는 첫 열에 있어야 합니다. - 조회값 입력: 예를 들어 F2 셀에 직원ID를 입력합니다.
- 공식(정확일치):
=VLOOKUP(F2, $A$2:$D$100, 3, FALSE) - 절대참조: 범위는
$로 고정해 복사 시 흔들림을 방지합니다. - 오류 시: 숫자↔텍스트 형식 불일치를 우선 점검하세요(아래 트러블슈팅 표 참고).
기본 개념과 문법
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- lookup_value: 찾을 값(예: 직원ID, 코드)
- table_array: 표 범위. 첫 열이 조회키여야 하며 반환 열은 그 오른쪽에 있어야 합니다.
- col_index_num: 반환 열 번호(범위의 왼쪽부터 1, 2, 3…)
- range_lookup:
FALSE(정확일치),TRUE(근사일치)
정확(FALSE) vs 근사(TRUE) — 선택 가이드
- 정확일치(FALSE): 정렬 불필요, 결과가 정확. 대용량(수만 행 이상)에서는 상대적으로 느릴 수 있습니다.
- 근사일치(TRUE): 키 열 오름차순 정렬이 필수. 내부적으로 이진탐색을 사용해 대량 데이터에서 훨씬 빠릅니다. 가격표/구간요금 등 “구간별” 조회에 유리합니다.
실무 예제 6종(재현 가능)
1) 직원ID → 부서 (정확일치)
| EmpID | Name | Dept |
|---|---|---|
| 1001 | J.Kim | Sales |
| 1002 | A.Lee | HR |
| 1003 | M.Park | Finance |
| 1004 | S.Choi | Sales |
| 1005 | E.Han | IT |
| 1006 | R.Lim | IT |
=VLOOKUP(F2, $A$2:$C$8, 3, FALSE) — F2=1005면 IT 반환.
2) 코드 → 가격표 (근사일치·정렬 유지)
코드 열을 오름차순으로 정렬한 뒤:
=VLOOKUP(H2, $B$2:$C$20000, 2, TRUE)- 대용량에서 매우 빠름(정렬 깨지면 오답 위험 → 정렬 자동화 매크로나 표 개체 사용 추천).
3) 두 방향 조회(VLOOKUP+MATCH)
머리행 B4:E4에 Jan, Feb, Mar, Apr가 있을 때:
=VLOOKUP(H4, $B$5:$E$16, MATCH(H5, $B$4:$E$4, 0), 0)H4(고객) 행과 H5(월) 열을 교차해 값을 반환.
4) 부분일치(와일드카드) 고객 찾기
*: 0개 이상 문자,?: 임의의 1자
=VLOOKUP("JK*", $A$2:$C$8, 2, FALSE)5) 왼쪽 조회(Left lookup) — 대안으로 해결
VLOOKUP은 반환 열이 오른쪽에 있어야 하므로 왼쪽 조회는 직접 불가. 아래 대안을 사용하세요.
- INDEX/MATCH:
=INDEX(B:B, MATCH(H2, C:C, 0)) - XLOOKUP:
=XLOOKUP(H2, C:C, B:B, "없음")(좌/우 모두 가능, if_not_found 메시지 제공)
6) 마지막 매칭(가장 아래 항목) 구하기
=INDEX($B$1:$B$20, MAX(IF($A$1:$A$20=H2, ROW($A$1:$A$20)-ROW($A$1)+1)))동적 배열(Office 365)이면 LET/FILTER 조합으로 가독성을 더 높일 수 있습니다.
트러블슈팅(증상 | 원인 | 해결)
| 증상 | 원인 | 해결 |
|---|---|---|
| #N/A | 값 없음, 숫자↔텍스트 형식 불일치 | 조회키·키열 형식 통일(필요 시 VALUE/TEXT), 데이터 > 텍스트 나누기로 일괄 정리 |
| #REF! | col_index_num가 범위를 초과 | 열 번호를 범위 내로 조정(표 개체/구조참조 사용 권장) |
| #VALUE! | 인수 형식 오류 | col_index_num 정수 여부·범위 참조 유효성 점검 |
| 틀린 값(근사) | 정렬 누락/깨짐 | 키 열 오름차순 유지, 업데이트 시 자동 정렬(매크로/Power Query) 도입 |
성능 최적화(대용량 핵심)
- 근사일치(TRUE) + 오름차순 정렬 = 이진탐색 기반으로 대폭 가속.
- 정확일치(FALSE)는 선형검색 → 10만 행 이상이면 체감 저하. 가능하면 XLOOKUP/XMATCH 검토.
- 데이터는 표 개체(CTRL+T)로 관리해 범위 자동 확장·구조참조 사용.
- 반복 계산 최소화: 보조열 생성(정규화), 계산 영역 분리, 필요 시 Power Query/피벗으로 전처리.
체크리스트(실수 방지)
- 조회키는 첫 열에, 반환 열은 오른쪽.
- 정확일치에서는
*/?와일드카드 사용 가능. - 근사일치는 오름차순 정렬이 생명(정렬 무너지면 오답).
col_index_num범위 초과 금지.- 왼쪽/마지막 조회는 INDEX/MATCH 또는 XLOOKUP로 대체.
관련 글
참고: 오빠두엑셀 VLOOKUP 페이지, Microsoft 공식 문서, Exceljet 함수 레퍼런스.