엑셀 XLOOKUP 심화 - 다중 조건 검색과 고급 활용

5편에서 XLOOKUP의 기본 사용법을 다뤘어요.
이번 편에서는 다중 조건 검색, 와일드카드, 가로 검색, 이진 검색, 중첩 XLOOKUP 등 고급 활용법을 정리합니다.

 

사용 가능 버전: Excel 365, Excel 2021 이상

XLOOKUP 문법 복습

=XLOOKUP(찾을값, 찾을범위, 반환범위, [못찾을때], [일치모드], [검색모드])

 

인수	기본값	설명
못찾을때	#N/A	값을 못 찾았을 때 반환할 값
일치모드	0 (정확히 일치)	-1=이하, 0=정확, 1=이상, 2=와일드카드
검색모드	1 (처음부터)	-1=끝에서부터, 2=이진검색(오름차순), -2=이진검색(내림차순)

다중 조건 검색

VLOOKUP/XLOOKUP은 기본적으로 하나의 조건만 검색해요.
하지만 "부서가 영업팀이면서 직급이 대리"인 사람을 찾으려면 두 가지 조건이 필요합니다.

 

방법 - 조건을 결합해서 검색

찾을범위에서 여러 열을 & 연산자로 결합하면 다중 조건 검색이 가능해요.

 

데이터:

	A	B	C	D
1	이름	부서	직급	매출
2	김민수	영업팀	대리	450
3	이지은	영업팀	과장	520
4	박준호	개발팀	대리	200

 

"영업팀"+"대리"의 매출을 찾기:

=XLOOKUP("영업팀"&"대리", B2:B10&C2:C10, D2:D10, "없음")

 

B열과 C열을 &로 결합해서 "영업팀대리"라는 키를 만들고, 찾을값도 "영업팀"&"대리"로 결합해요.
결과: 450 (김민수)

 

셀 참조를 사용하면:

=XLOOKUP(F2&G2, B2:B10&C2:C10, D2:D10, "없음")

F2에 "영업팀", G2에 "대리"가 입력되어 있으면 동일한 결과예요.

 

주의: 이 수식은 Ctrl+Shift+Enter 없이 일반 Enter로 입력하면 됩니다 (365/2021).

와일드카드 검색

일치모드를 2로 설정하면 와일드카드를 사용할 수 있어요.

 

=XLOOKUP("김*", A2:A10, D2:D10, "없음", 2)

"김"으로 시작하는 첫 번째 이름의 매출을 반환해요.

 

와일드카드	의미
*	임의의 문자열 (0자 이상)
?	임의의 문자 1개

19편의 찾기·바꾸기에서 다룬 와일드카드와 동일한 규칙입니다.

마지막 일치값 찾기 (역방향 검색)

기본 XLOOKUP은 첫 번째 일치값을 반환해요.
검색모드를 -1로 설정하면 마지막 일치값을 찾습니다.

 

=XLOOKUP("영업팀", B2:B10, A2:A10, "없음", 0, -1)

영업팀에 해당하는 마지막 사람의 이름을 반환해요.

 

활용: 최신 거래 내역 찾기, 마지막 입금일 찾기 등

이진 검색 (대용량 데이터 고속 검색)

데이터가 정렬되어 있으면 이진 검색을 사용해서 속도를 높일 수 있어요.

 

=XLOOKUP(찾을값, 찾을범위, 반환범위, , 0, 2)

검색모드 2: 오름차순 정렬된 데이터에서 이진 검색
검색모드 -2: 내림차순 정렬된 데이터에서 이진 검색

 

10만 행 이상의 데이터에서 체감 차이가 생겨요.
단, 데이터가 정렬되어 있지 않으면 잘못된 결과를 반환하므로 주의하세요.

근사 검색 - 이하/이상 매칭

일치모드를 -1(이하) 또는 1(이상)로 설정하면 근사값 검색이 가능해요.

 

세율 테이블 예시:

	F	G
1	과세표준	세율
2	0	6%
3	14000000	15%
4	50000000	24%
5	88000000	35%

 

소득이 30,000,000원일 때 해당 세율:

=XLOOKUP(30000000, F2:F5, G2:G5, , -1)

결과: 15% (30,000,000 이하인 값 중 가장 큰 14,000,000에 매칭)

 

VLOOKUP의 TRUE(근사 일치)와 같은 동작이지만, XLOOKUP은 데이터가 반드시 오름차순일 필요가 없어요.

XLOOKUP 중첩 - 행과 열 교차 검색

행 방향과 열 방향 동시에 검색하는 2차원 검색이에요.

 

데이터 (월별/부서별 매출):

	A	B	C	D
1		1월	2월	3월
2	영업팀	300	450	380
3	개발팀	200	210	250
4	기획팀	150	180	170

 

"개발팀"의 "2월" 매출:

=XLOOKUP("개발팀", A2:A4,
  XLOOKUP("2월", B1:D1, B2:D4))

 

안쪽 XLOOKUP이 "2월" 열을 찾아 해당 열의 배열(450,210,180)을 반환하고, 바깥 XLOOKUP이 그 배열에서 "개발팀" 행을 찾아 210을 반환해요.

 

8편의 INDEX+MATCH 조합과 같은 역할이지만, XLOOKUP 중첩이 더 직관적이에요.

XLOOKUP으로 여러 열 반환

반환범위를 여러 열로 지정하면 한 번에 여러 값을 반환해요.

=XLOOKUP(A2, D2:D100, E2:G100)

E, F, G 3개 열의 값이 동시에 반환됩니다 (스필).

자주 하는 실수 / 주의사항

1. 다중 조건 검색에서 구분자 없이 결합하면 오매칭

"영업"&"팀대리" = "영업팀"&"대리" = "영업팀대리"
다른 조합이 같은 결합값을 만들 수 있어요.

구분자를 추가하면 방지할 수 있습니다:

=XLOOKUP("영업팀|대리", B2:B10&"|"&C2:C10, D2:D10)

2. 이진 검색은 반드시 정렬된 데이터에서만

정렬되지 않은 데이터에 이진 검색을 사용하면 잘못된 결과가 나와요.
확실하지 않으면 기본 검색모드(1)를 사용하세요.

3. XLOOKUP은 전체 열 참조(A:A)보다 범위 지정이 빠름

=XLOOKUP(A2, B:B, C:C) 보다 =XLOOKUP(A2, B2:B1000, C2:C1000)이 빨라요.

관련 함수 안내

5편(XLOOKUP 기본), 8편(INDEX+MATCH), 1편(VLOOKUP)과 함께 보면 검색 함수의 전체 그림을 잡을 수 있어요.
18편의 IFERROR 대신 XLOOKUP의 네 번째 인수(못찾을때)를 활용하면 수식이 더 간결해집니다.

 

다음 글부터는 엑셀 트러블슈팅 시리즈가 시작됩니다.
느린 파일 최적화, 깨진 데이터 복구, 흔한 오류 해결 모음을 다룰 예정이에요.