엑셀 XLOOKUP 함수 완벽 가이드
엑셀을 조금만 다뤄본 사람이라면 VLOOKUP 함수는 한 번쯤 들어봤을 겁니다. 하지만 2019년 이후부터는 XLOOKUP이라는 새로운 함수가 등장하면서, 기존의 VLOOKUP/HLOOKUP을 대체하는 차세대 검색 함수로 자리 잡았습니다.
오늘은 XLOOKUP을 완전히 정복할 수 있도록, 기본부터 사람들이 잘 모르는 심화 사용법까지 아주 길게 설명드리겠습니다.
1. XLOOKUP 함수란?
정의: 지정한 검색 값(Lookup_value)을 특정 범위(Lookup_array)에서 찾아, 그와 연결된 다른 범위(Return_array)의 값을 반환하는 함수
등장 배경: VLOOKUP은 오랫동안 엑셀 검색의 표준 함수였지만, 여러 가지 제약 때문에 불편한 점이 많았습니다.
열 번호를 수동으로 입력해야 했고,
왼쪽 열을 검색할 수 없었으며,
기본 동작이 ‘근사치 검색’이라 실수가 잦았습니다.
이 모든 단점을 해결하기 위해 XLOOKUP이 나온 것이죠.
2. XLOOKUP 함수 기본 구조
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
각 인수의 의미는 다음과 같습니다.
lookup_value → 찾고자 하는 값
lookup_array → 찾을 범위(한 행 또는 한 열)
return_array → 결과를 가져올 범위(lookup_array와 크기가 일치해야 함)
if_not_found (선택) → 검색 결과가 없을 때 표시할 값
match_mode (선택) → 검색 방식 지정
0: 정확히 일치 (기본값)
-1: 정확히 일치하거나, 없으면 더 작은 값
1: 정확히 일치하거나, 없으면 더 큰 값
2: 와일드카드 문자 허용 (*, ?, ~)
search_mode (선택) → 검색 방향 및 알고리즘
1: 위에서 아래(또는 왼쪽에서 오른쪽) 순차 검색 (기본)
-1: 아래에서 위(또는 오른쪽에서 왼쪽) 역순 검색
2: 이진 검색 (정렬 오름차순 가정)
-2: 이진 검색 (정렬 내림차순 가정)
3. VLOOKUP vs XLOOKUP 차이
즉, XLOOKUP은 VLOOKUP/HLOOKUP의 모든 단점을 보완한 차세대 함수입니다.
4. 사람들이 잘 모르는 XLOOKUP 기능
(1) if_not_found 옵션
=XLOOKUP("홍길동", A:A, B:B, "미등록 고객")
→ 값이 없을 때 단순히 #N/A가 아닌 "미등록 고객"이라는 메시지를 반환.
이건 실무에서 오류 보고서를 깔끔하게 만들 때 굉장히 유용합니다.
(2) 와일드카드 검색
match_mode = 2를 쓰면 부분 일치 검색이 가능합니다.
=XLOOKUP("*김*", A:A, B:B, "없음", 2)
→ "김"이 포함된 모든 항목을 찾아 반환.
(*는 0개 이상의 임의 문자열, ?는 임의의 한 글자)
(3) 역순 검색
=XLOOKUP("서울", A:A, B:B, "없음", 0, -1)
→ A:A에서 "서울"을 찾되, 마지막에 등장한 값을 반환.
이전에는 LOOKUP(2,1/(조건식), 범위) 같은 꼼수를 썼는데, 이제는 깔끔하게 가능해졌습니다.
(4) 이진 검색
데이터가 정렬되어 있다면 search_mode = 2 또는 -2를 쓰면 검색 속도가 폭발적으로 빨라집니다.
예를 들어 수십만 행짜리 데이터에서도 거의 즉시 결과가 나옵니다.
=XLOOKUP(1000, A:A, B:B, "없음", 0, 2)
→ A열이 오름차순 정렬되어 있으면, 1000을 매우 빠르게 찾습니다.
(5) 다중 범위 반환
XLOOKUP은 단일 값뿐만 아니라 여러 열을 한 번에 반환할 수 있습니다.
=XLOOKUP("홍길동", A:A, B:D)
→ 홍길동의 이름이 A열에 있다면, B~D열 전체를 한 번에 반환합니다.
엑셀 365의 동적 배열 기능과 결합되면 강력한 다차원 검색이 가능해집니다.
(6) 중첩 XLOOKUP
2차원 검색(행+열 동시 검색)도 가능합니다.
=XLOOKUP("수학", B1:F1, XLOOKUP("홍길동", A2:A100, B2:F100))
→ 행 기준으로는 "홍길동", 열 기준으로는 "수학"을 찾아 해당 교차값을 반환.
이전에는 INDEX + MATCH 2개를 중첩해야 했는데, XLOOKUP 하나로 깔끔하게 해결됩니다.
5. 실무 활용 예제
(1) 전화 국가 코드를 불러와 본다고 가정
(2) 월별 매출 불러오기
=XLOOKUP(202501, 매출!A:A, 매출!D:D, 0)
→ "2025년 1월" 코드가 있으면 매출을 불러옴.
(3) 마지막 주문 내역 찾기
=XLOOKUP("홍길동", 주문!B:B, 주문!E:E, "없음", 0, -1)
→ 홍길동이 가장 마지막에 주문한 상품명 반환.
(4) 범위 기반 가격표 검색
=XLOOKUP(75, {0,50,100,200}, {"일반","실버","골드","VIP"}, "없음", 1)
→ 75라는 값이 들어오면 "실버"를 반환. (구간별 등급 판정 가능)
6. 성능과 호환성
지원 버전: Excel 365, Excel 2021 이후 정식 탑재. (Excel 2019 일부 업데이트 버전도 지원)
호환성 문제: 구버전 Excel(2016 이하)에서는 XLOOKUP을 인식하지 못하므로, 공유할 때 주의해야 합니다.
성능: 대용량 데이터에서 VLOOKUP 대비 확실히 빠릅니다. 특히 이진 검색 모드(2/-2)와 함께 쓰면 초고속.
7. 마무리
XLOOKUP은 단순한 "VLOOKUP의 개선판"이 아닙니다.
양방향 검색 가능
부분 일치 지원
마지막 값 검색 지원
다차원 배열 반환 가능
검색 실패 시 사용자 메시지 반환 가능
즉, 이제부터는 모든 검색 작업에서 XLOOKUP이 사실상 표준이라고 봐야 합니다.
아직도 VLOOKUP/HLOOKUP을 습관적으로 쓰고 계신다면, 지금이라도 XLOOKUP으로 갈아타는 걸 강력히 추천드립니다. 실무 효율성이 확연히 달라질 겁니다.
