XLOOKUP 함수 완벽 가이드 — 정확/근사·다중값·역방향·이진탐색
XLOOKUP 함수는 범위/배열에서 값을 찾아 첫 일치 항목의 결과를 반환하는 최신 조회 함수입니다. 기본이 정확일치이며, if_not_found로 오류 문구를 지정하고, match_mode/search_mode로 근사·와일드카드·역방향·이진탐색까지 제어할 수 있습니다. (공식 정의)
→ 심화 비교는 XLOOKUP vs VLOOKUP에서 이어서 보세요.
Quick Fix: 2분 설치(정확일치 + 메시지)
- 조회값 입력: 예) H2 = 사원ID
- 기본 공식:
=XLOOKUP(H2, $A:$A, $E:$E, "없음") - 포인트: 기본은 정확일치, 못 찾으면 “없음” 반환 → #N/A 방지.
문법·인수 총정리
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])- lookup_value: 찾을 값
- lookup_array: 찾을 범위(열/행)
- return_array: 반환 범위(열/행)
- if_not_found: 못 찾았을 때 표시
- match_mode: 0=정확, -1=다음 작은 값, 1=다음 큰 값, 2=와일드카드
- search_mode: 1=처음→끝, -1=끝→처음, 2=이진(오름차순), -2=이진(내림차순)
match_mode 빠르게 이해
- 0(기본): 정확일치
- ±1: 근사일치(오름/내림 정렬 전제)
- 2:
*,?,~와일드카드
search_mode 제대로 쓰기
- -1: 역방향 검색(마지막 값 우선)
- ±2: 이진탐색 — 정렬이 필수이며 미정렬이면 오답
실무 예제 7종(재현 가능)
1) 기본 조회(사원ID→부서)
| EmpID | Name | Dept | |
|---|---|---|---|
| 1001 | J.Kim | Sales | j.kim@ex.com |
| 1002 | A.Lee | HR | a.lee@ex.com |
| 1003 | M.Park | Finance | m.park@ex.com |
| 1004 | S.Choi | Sales | s.choi@ex.com |
=XLOOKUP(H2, $A:$A, $C:$C, "없음") → H2=1003이면 Finance
2) 다중값 반환(한 번에 3열 채우기)
H6 셀 하나에 입력하고 I6:J6로 자동 확장됩니다(동적 배열).
=XLOOKUP(H6, $A:$A, $B:$D, "없음")3) 근사일치(구간요금/가격표)
키 열을 정렬한 뒤:
=XLOOKUP(H10, $B:$B, $C:$C, "없음", -1)-1은 “정확 또는 다음 작은 값”을 반환(오름차순 정렬 필요).
4) 역방향 검색(마지막 매칭 우선)
=XLOOKUP(H12, $A:$A, $E:$E, "없음", 0, -1)search_mode=-1로 아래에서 위로 검색 → “가장 마지막” 값에 강함.
5) 두 방향(행·열) 교차 조회
=XLOOKUP(H14, $B$5:$B$12, XLOOKUP(H15, $C$4:$G$4, $C$5:$G$12), "없음")6) 와일드카드/부분일치
=XLOOKUP("*"&H16&"*", $B:$B, $D:$D, "없음", 2)match_mode=2는 */? 와일드카드를 사용합니다.
7) 다중 조건(AND/OR)
AND(곱셈) 패턴:
=XLOOKUP(1, (Region="East")*(Account="X*"), Price, "없음", 0, 1)OR는 더하기 사용:
=XLOOKUP(1, (Region="East") + (Region="West"), Price, "없음")트러블슈팅
| 증상 | 원인 | 해결 |
|---|---|---|
| #N/A | 값 부재/오타/형식 불일치 | if_not_found로 메시지 지정, 조회·반환 범위 행수 일치, 숫자↔텍스트 정리 |
| 오답(근사/이진) | 정렬 누락 | match_mode ±1, search_mode ±2는 정렬 필수 |
| 확장 안 됨 | 동적 배열 비활성 | Microsoft 365/Excel 2021 이상 또는 영역을 표(CTRL+T)로 관리 |
| 마지막 값 필요 | 기본은 처음→끝 | search_mode=-1 사용(역방향) |
VLOOKUP과의 선택 가이드 + XMATCH 팁
- 왼쪽/오른쪽 모두 가능(범위만 지정) — VLOOKUP의 좌측열 제약 없음.
- 오류 메시지를
if_not_found로 직접 제어. - 다중열 반환으로 반복 수식 감소.
- XMATCH와 결합 시 검색 위치/정렬 모드를 세밀 제어.
체크리스트 & 성능 팁
- 조회/반환 범위의 행 수는 반드시 같게.
- 근사/이진 검색은 정렬을 습관화.
- 중복키가 잦으면 역방향(-1)으로 “마지막” 우선.
- 데이터는 표(CTRL+T)로 관리해 자동 확장·구조참조 활용.
관련 글
참고/벤치마크: Data Science Diary(2023-04-11), Microsoft Support XLOOKUP, Exceljet의 XLOOKUP 예제 모음.