XLOOKUP · XMATCH로 조회 끝판왕 (양방향·가변열·N번째·마지막 1분 컷)
이제 더 이상 VLOOKUP 열 번호 외우지 마세요. XLOOKUP은 왼쪽/오른쪽 모두 조회하고, 기본값·다중 열 반환·마지막/N번째까지 한 번에 해결합니다.
문법 & 핵심 옵션
| 함수 | 형식 | 포인트 |
|---|---|---|
| XLOOKUP | =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) | 기본값, 정확/근사, 앞/뒤 방향 검색, 다중 열 반환(스필) |
| XMATCH | =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) | 상대 위치(인덱스) 반환, 최근/이전 방향 검색 가능 |
match_mode: 0 정확/ -1 이하 근사/ 1 이상 근사/ 2 와일드카드. search_mode: 1 앞으로/ -1 뒤로(마지막부터) 검색.
기본 조회(정확/근사)
예제 1) 제품코드로 가격 찾기(정확)
=XLOOKUP(F2, Products[Code], Products[Price], "없음")예제 2) 구간표 근사값(점수→등급)
=XLOOKUP(F2, Grade[Cutoff], Grade[Level], , -1) // 이하 근사양방향 & 다중 열 반환
예제 3) 고객명으로 주소·연락처 동시에 반환
=XLOOKUP(F2, 고객[이름], HSTACK(고객[주소], 고객[전화])) // 스필예제 4) 행·열 모두 기준인 2D 조회
=XLOOKUP(제품, 테이블[제품], XLOOKUP(지역, 테이블[[#Headers],[서울]:[부산]], 테이블[[서울]:[부산]]))여러 조건(AND/OR) 조회
예제 5) 지역+상품 조합 키
=XLOOKUP(F2&"|"&G2, 지역열&"|"&상품열, 매출)예제 6) OR(여러 후보 중 첫 일치)
=XLOOKUP({F2,G2,H2}, 코드열, 가격) // 첫 스필이 일치하면 반환N번째·마지막 항목
예제 7) 마지막 주문 금액(고객=F2)
=XLOOKUP(F2, 고객열, 금액열, , 0, -1) // 뒤에서 앞으로 검색예제 8) N번째 일치(고객=F2, N=K2)
=LET(m, FILTER(금액열, 고객열=F2), INDEX(m, K2))XMATCH로 위치 계산
예제 9) 제품의 열 위치를 찾아 CHOOSECOLS에 전달
=LET(p, XMATCH(G2, Table[#Headers]), TAKE(CHOOSECOLS(Table, p), , ))예제 10) 정렬 후 이진검색(대용량 가속)
=XMATCH(F2, SORTBY(코드열, 코드열), 0, 2) // 2=이진 검색(오름차순)실수·성능 체크리스트
- 텍스트 숫자 → VALUE/TEXT로 형식 통일 후 조회.
- 범위 크기 → lookup_array와 return_array 길이 일치.
- 기본값 →
if_not_found에 메시지/값 제공(보고서 품질 ↑). - 성능 → 자주 쓰는 범위는
LET으로 캐시, 필요 시search_mode=2(이진검색) + 정렬. - 다중 열 반환 → 스필 범위가 겹치지 않도록 우측/하단 비우기.
요약 정리
| 목표 | 대표 공식 |
|---|---|
| 정확 조회 + 기본값 | XLOOKUP(key, 찾을열, 반환열, "없음") |
| 근사 구간 | XLOOKUP(x, 기준열, 결과열, , -1/1) |
| 마지막 일치 | XLOOKUP(key, 열, 값, , 0, -1) |
| 여러 조건 | XLOOKUP(조건1&"|"&조건2, 열1&"|"&열2, 값) |
| 다중 열 반환 | XLOOKUP(key, 열, HSTACK(반환1,반환2,...)) |
FAQ
동일 키가 중복이면 어떤 행이 반환되나요?
기본은 첫 일치, search_mode=-1이면 마지막 일치가 반환됩니다. 모든 행이 필요하면 FILTER를 사용하세요.
열 추가/순서 변경에 안전한가요?
예. XLOOKUP은 범위 참조 기반이라 VLOOKUP의 col_index_num 문제를 피합니다.