완전 새로운 엑셀 XLOOKUP 함수 쉽게 배우기

엑셀 XLOOKUP 함수란?

XLOOKUP은 검색 테이블에서 값을 찾아 반환 배열의 해당 위치 값을 반환하는 차세대 조회 함수입니다. VLOOKUP과 HLOOKUP의 모든 제약을 해결하여 양방향 검색, 역방향 검색, 와일드카드 매칭, 근사 일치를 모두 지원합니다. 향상된 많은 기능들을 지원할 수 있고, 검색 방향과 배열 모드를 자유롭게 설정할 수 있어 모든 유형의 데이터 조회 작업에서 가장 강력하고 유연한 솔루션을 제공합니다.
 

엑셀 VLOOKUP함수 쉽게 배우기

 
 
 

엑셀 XLOOKUP 함수 구문 이해하기

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: 일치 방식 설정 (선택)
• search_mode: 검색 방향 및 방식 (선택)

 
 

엑셀 XLOOKUP 함수 작동원리

chanisheet 예제를 살펴보면 B5 셀에 =XLOOKUP(A5, A13:A16, B13:B16) 함수가 사용되었습니다. 이 함수의 작동 원리를 단계별로 살펴보겠습니다

1. 검색값 식별: 함수는 먼저 A5 셀에서 검색할 값을 가져옵니다. 예제에서는 "P103"입니다.
2. 검색 범위에서 값 찾기: lookup_array인 A13:A16 범위에서 "P103" 값을 찾습니다. 이 값은 A14 셀에 있습니다.
3. 해당 위치 파악: 함수는 "P103"이 검색 범위의 두 번째 위치(인덱스 2)에 있음을 파악합니다.
4. 반환값 결정: 동일한 위치(인덱스 2)에 있는 return_array(B13:B16)의 값을 가져옵니다. 이 값은 B14 셀의 "마우스"입니다.
5. 결과 반환: 최종적으로 B5 셀에 "마우스"라는 결과가 표시됩니다.

 

이 과정에서 XLOOKUP의 가장 큰 장점은 검색 열(A13:A16)과 반환 열(B13:B16)이 서로 독립적으로 지정된다는 점입니다. 이로 인해 VLOOKUP과 달리 테이블 구조나 열의 순서에 제약받지 않고 원하는 값을 자유롭게 검색할 수 있습니다.
 
예를 들어, 같은 데이터에서 제품 코드로 키보드의 위치를 찾으려면

* A5에 "P105" 입력
* 함수는 A13:A16에서 "P105"를 찾고(A15 셀)
* B13:B16의 같은 위치(B15 셀)에서 "키보드" 값을 반환

 
 
 

엑셀 XLOOKUP 고급 매개변수 활용하기

 
1. if_not_found: 값을 찾지 못했을 때 반환할 값

* 예: =XLOOKUP("P999", A13:A16, B13:B16, "없음") → "P999"가 없으면 "없음" 반환
* #N/A 오류 대신 사용자 지정 메시지나 값 반환 가능

 

2. match_mode: 일치 방식 설정

* 0: 정확히 일치 (기본값)
* -1: 정확히 일치하거나 다음으로 작은 값
* 1: 정확히 일치하거나 다음으로 큰 값
* 2: 와일드카드 사용 가능 (* 및 ? 지원)

 

3.search_mode: 검색 방향 및 방식

* 1: 처음부터 검색 (기본값)
* -1: 마지막부터 검색
* 2: 이진 검색(오름차순 정렬 가정)
* -2: 이진 검색(내림차순 정렬 가정)

 
 

엑셀 XLOOKUP과 VLOOKUP 비교

XLOOKUP은 VLOOKUP의 모든 제한을 극복하여 더 강력하고 유연한 검색 기능을 제공합니다
 
1. 검색 방향

VLOOKUP: 항상 왼쪽 열에서 오른쪽으로만 검색
XLOOKUP: 어떤 방향으로든 검색 가능 (좌→우, 우→좌, 위→아래, 아래→위)

 
2. 검색 방식

VLOOKUP: 첫 번째 열에서만 검색 가능
XLOOKUP: 임의의 열이나 행에서 검색 가능

 
3. 반환 방식

VLOOKUP: 열 번호로 반환 열 지정
XLOOKUP: 반환 배열 직접 지정

 
4. 오류 처리

VLOOKUP: 값 못 찾으면 #N/A 반환
XLOOKUP: 사용자 지정 메시지나 값 반환 가능

 
5. 와일드카드 및 근사 검색

XLOOKUP에서 더 유연하게 지원

 

엑셀 XLOOKUP과 FILTER 함수 조합 활용하기

 
XLOOKUP과 FILTER 함수를 조합하면 더욱 강력한 데이터 검색 및 필터링 기능을 구현할 수 있습니다. FILTER 함수는 조건에 맞는 항목을 모두 추출할 수 있어, XLOOKUP과 함께 사용하면 복잡한 조건을 가진 검색 작업을 간단하게 처리할 수 있습니다.

=XLOOKUP(lookup_value, FILTER(lookup_array, include), FILTER(return_array, include))

 

• 다중 조건 검색 가능
• 검색 범위를 동적으로 필터링하여 정확도 향상
• 대용량 데이터에서 검색 속도 향상
• 부서/카테고리 등 조건에 맞는 하위 집합에서만 검색 가능

XLOOKUP과 FILTER 조합은 특히 대규모 데이터셋에서 특정 조건에 맞는 항목만 검색할 때 매우 유용합니다.
 
 
 

Chanisheet로 엑셀 XLOOKUP 실습하기

 

ChaniSheet - 귀차니들을 위한 엑셀 자동화 학습

 
chanisheet는 XLOOKUP 함수를 실습할 수 있는 이상적인 환경을 제공합니다. 화면에서 볼 수 있듯이, 왼쪽에는 실제 함수가 구현된 엑셀 시트가, 오른쪽에는 함수 설명과 매개변수 정보가 제공됩니다.
 

 

 

엑셀 XLOOKUP 함수 활용 팁과 주의사항

 
1. 가용성 확인

XLOOKUP은 Excel 2019 이전 버전에서는 사용할 수 없음
Microsoft 365 구독자나 Excel 2021 이상 버전에서 사용 가능

 
2. 성능 최적화

대규모 데이터셋에서는 search_mode를 2나 -2로 설정하여 이진 검색 활용
데이터가 정렬되어 있다면 검색 속도가 크게 향상됨

 
3. 여러 조건 검색

XLOOKUP 안에 다른 XLOOKUP 중첩하여 여러 조건 검색 가능
=XLOOKUP(A5&B5, A13:A16&B13:B16, C13:C16)

 
4.배열 수식으로 활용

=XLOOKUP($A$5:$A$7, $A$13:$A$16, $B$13:$B$16)
한 번에 여러 값 검색하여 결과 배열로 반환

XLOOKUP은 VLOOKUP, HLOOKUP, INDEX-MATCH 조합을 대체할 수 있는 강력한 함수로, 데이터 검색 작업을 더욱 효율적이고 유연하게 만들어줍니다.