엑셀 XLOOKUP 함수 완전 정복 — 좌/우·양방향·와일드카드·마지막 일치·배열반환
XLOOKUP은 VLOOKUP/HLOOKUP/LOOKUP/INDEX+MATCH를 대체하는 현대적 조회 함수입니다. 왼쪽/오른쪽·가로/세로 어디든 찾고, 정확/근사·와일드카드·마지막 일치, 심지어 여러 열을 배열로 반환할 수 있습니다.
Quick Fix: 현업 최다 6패턴
=XLOOKUP($G2, 제품코드, 제품명) ; 기본
=XLOOKUP($G2, 제품명, 출시일) ; 왼쪽(역방향)
=XLOOKUP($G2, 주문코드, 상태, , , -1) ; 마지막 일치
=XLOOKUP("Hoodie*", 상품명, 가격, "없음", 2) ; 부분일치(시작)
=XLOOKUP($G2, 월_헤더행, 매출_행) ; 가로
=XLOOKUP($G2, 제품코드, 제품명:납품처) ; 배열 반환
구문/인수 핵심
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])- match_mode: 0 정확(기본), -1/1 근사, 2 와일드카드
- search_mode: 1 위→아래(기본), -1 아래→위(마지막), 2/-2 이진검색
좌/우·양방향 & 마지막 일치
반환 열의 위치 제약이 없고, search_mode=-1로 마지막 일치 검색이 가능합니다.
와일드카드/부분 일치
=XLOOKUP("*Denim*", 상품명, 가격, "없음", 2)가로 방향 & 배열 반환
=XLOOKUP($G2, 월_헤더행, 매출_행)
=XLOOKUP($G2, 제품코드, 제품명:납품처)다중 조건(AND/OR), 근사+필터 혼합
=XLOOKUP(1,(카테고리=H5)*(색상=H6)*(사이즈=H7), 가격) ; AND
=XLOOKUP(1,(색상="Red")+(색상="Pink"), 가격) ; OR
=XLOOKUP(H7, IF(서비스=H6, 무게), 비용, , -1) ; 근사일치(+정렬)실무 예제
| Item | Color | Size | Price |
|---|---|---|---|
| Hoodie | Blue | M | 29 |
| Hoodie | Blue | L | 31 |
| Hoodie | Red | M | 28 |
=XLOOKUP(1,(A:A="Hoodie")*(B:B="Blue")*(C:C="L"),D:D) → 31
=XLOOKUP("Hoodie*",A:A,D:D,"없음",2) → 29 (첫 일치)
=XLOOKUP("Hoodie",A:A,D:D,"없음",0,-1) → 28 (마지막)트러블슈팅 & 성능
| 증상 | 원인 | 해결 |
|---|---|---|
| #N/A | 공백/형식·불일치 | TRIM/CLEAN·형식 통일, IFNA |
| 느림 | 문자열결합/광범위 | 불리언 곱, 범위 최소화, 표 참조 |
| 근사 오답 | 정렬 전제 위반 | 열 정렬 + match_mode 확인 |
| 마지막값 미탐 | 기본 위→아래 | search_mode=-1 |
대안/확장
- FILTER: 행 집합 추출 후 선택
- XMATCH: 위치만 찾아 INDEX로 반환
- LET: 조건/범위를 변수화해 성능·가독성 향상
관련 글
참고: 오빠두닷컴 XLOOKUP 사용법/총정리, Microsoft/Exceljet 공식 문서.