엑셀 XLOOKUP 함수 고급 활용법: 동적 배열, 부분 문자열, 다중 기준 검색

엑셀의 XLOOKUP 함수는 기본적인 검색 기능을 넘어 더 강력하고 유연한 활용이 가능합니다. 이 글에서는 XLOOKUP의 고급 활용법으로 동적 배열 검색, 부분 문자열 검색, 다중 기준 검색에 대해 알아보겠습니다.

📌 XLOOKUP 함수의 기본 사용법, 구문, 인수에 대한 설명은 XLOOKUP 함수 기본편을 참조 바람.

 

1️⃣ 동적 배열 검색

XLOOKUP 함수는 Excel 365의 동적 배열 기능과 함께 사용하면 더욱 강력해진다. 하나의 수식으로 여러 검색 결과를 동시에 반환할 수 있어 복잡한 데이터 분석 작업을 간소화할 수 있다.

📊 업무 활용 예제: 분기별 실적 조회

분기별 영업 실적 데이터에서 특정 지역의 모든 분기 실적을 한번에 조회하는 방법을 알아본다.

예제1 : 분기별 실적 조회

 

위와 같은 데이터에서 "서울" 지역의 모든 분기 실적을 한번에 조회하려면 다음과 같이 XLOOKUP 함수를 사용한다.

=XLOOKUP("서울", B2:B8, C2:F8)

 

이 수식은 "서울" 을 찾아 해당 행의 모든 분기 실적(C~F열)을 동시에 반환한다.

1. B13 셀이 포인터를 놓는다.
2. 수식을 입력한다.
3. 결과가 동적 배열(반환값의 범위가 C열~F열)로 1분기~4분기 까지 반환된다.

예제1 결과 : 동적 배열 분기별 실적 조회

 

결과에서 보이듯 B13 단일 셀에 수식을 입력했는데 B13:F13 까지 값들이 채워진다. B13 셀을 선택하면 이 영역이 파란색 박스로 보인다. 이것이 엑셀이 버전업되면서 추가된 기능인 동적 배열이다.

동적 배열 검색을 활용하면 다음과 같은 장점이 있다:

• 여러번의 XLOOKUP 함수를 사용할 필요 없이 한 번의 수식으로 여러 결과 도출
• 데이터 변경 시 자동으로 결과 업데이트
• 보고서 작성 시간 단축

 

2️⃣ 부분 문자열 검색

엑셀 XLOOKUP 함수의 네 번째 인수인 [match_mode]를 활용하면 부분 문자열 검색이 가능하다. 특히 와일드카드(*)와 함께 사용하면 더욱 유연한 검색이 가능하다.

📊 업무 활용 예제: 제품 코드로 정보 찾기

제품 코드의 일부만 알고 있을 때 관련 제품 정보를 찾는 방법을 알아본다.

예제2 : 와일드 카드 검색 제품 코드 찾기

 

제품 코드에 "KA103"이 포함된 제품의 정보를 찾으려면 다음과 같이 XLOOKUP 함수를 사용한다:

=XLOOKUP("KA103*", B2:B10, C2:E10, "해당 제품 없음", 2)

1. C15 셀에 포인터를 놓는다.
2. 위의 수식을 입력한다.
3. 제품코드 중 "KA103" 으로 시작하는 첫번째 행을 찾아 반환한다.
4. 동적배열이 반환되기에 3열(제품명,가격,재고수량)이 결과로 표시된다.

여기서 match_mode 옵션 인수를 2로 설정하면 와일드카드를 사용한 검색이 가능합니다:

예제2 결과 : 와일드 카드 검색으로 부분문자열 검색한 결과

 

부분 문자열 검색의 주요 활용 분야:

• 대용량 제품 데이터베이스에서 빠른 검색
• 불완전한 정보로도 관련 데이터 찾기
• 고객 이름이나 주소 일부만 알고 있을 때 고객 정보 찾기

 

3️⃣ 다중 기준 검색

여러 조건을 동시에 만족하는 데이터를 찾아야 할 때는 XLOOKUP 함수와 함께 &(연결 연산자)나 CHOOSE 함수를 활용할 수 있다. 여기서는 & 연산자를 이용한 다중 검색법만 다룬다.

📊 업무 활용 예제: 특정 부서와 직급 조합으로 직원 찾기

직원 데이터베이스에서 특정 부서와 직급을 동시에 만족하는 직원을 찾는 방법을 알아본다.

"인사" 부서의 "부장" 직급인 직원을 찾으려면 다음과 같이 XLOOKUP 함수를 사용한다:

=XLOOKUP("인사"&"부장", B2:B15&C2:C15, A2:E15, "해당 직원 없음")

 

여기서 핵심은 B열(부서)과 C열(직급)을 연결하여 검색 키로 사용한다는 것이다.

1. C19 셀에 포인터를 놓는다.
2. 위의 함수식을 입력한다.
3. 검색_값도 부서와 직급을 '&' 로 연결한다.
4. 반환 범위는 이름으로 설정한다.
5. 검색 직원이 없다면 '직원 없음' 을 반환한다.

예제3 : 앰퍼샌드 연산자를 활용한 다중 검색 찾기

 

다중 기준 검색의 장점:

• 복잡한 데이터에서 정확한 정보 추출
• IF 함수의 중첩 사용 감소
• 데이터 분석 효율성 향상

 

💡 마무리

엑셀 XLOOKUP 함수의 고급 활용법을 통해 업무 효율성을 크게 높일 수 있다. 동적 배열 검색으로 여러 결과를 한 번에 확인하고, 부분 문자열 검색으로 불완전한 정보에서도 데이터를 찾을 수 있으며, 다중 기준 검색으로 복잡한 조건의 데이터를 정확히 추출할 수 있다.

Excel 2019 이상, Excel 365 사용자라면 이러한 고급 기능을 활용하여 데이터 분석 업무를 더욱 효율적으로 수행해 보자. VLOOKUP, HLOOKUP, INDEX-MATCH 조합보다 더 강력하고 직관적인 XLOOKUP 함수의 매력에 빠져보길 바란다.

📢 궁금한 점이나 추가 활용법이 있으시면 댓글로 남겨주세요. 다음 글에서 더 유용한 Excel 팁을 소개해드리겠습니다!

 

마늘빵