이제는 꼭 알아야 할, XLOOKUP 함수 실전예제 및 응용방법 총정리!

2020년 2월 공개된 XLOOKUP 함수의 기초 및 실전 응용방법을 단계별로 알아봅니다.

홈페이지 » 이젠 꼭 알아야 할, XLOOKUP 함수 실전예제 및 응용 총정리

엑셀 신규함수, XLOOKUP 함수 실전예제 및 응용방법 총정리

XLOOKUP 함수 예제 목차 바로가기
강의요약

2020년 2월 10일자로 오피스 365 월단위채널(공식 채널)에 XLOOKUP 함수가 업데이트 되었습니다. XLOOKUP 함수는 이번에 업데이트 된 동적배열함수 가장 중요한 함수로서, 이제 실무자라면 반드시 알아야 할 필수함수가 되었습니다.

이번 강의에서는 XLOOKUP 함수의 실전예제 및 동적배열함수의 특성을 이용한 응용방법 2가지를 각 단계별로 알아보겠습니다.

엑셀 XLOOKUP 함수는 20년 2월 기준, 오피스 365 구독자에게만 공개된 함수이므로 엑셀 2019 버전을 포함한 기존의 설치형 엑셀 사용자는 사용이 불가합니다.

영상강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [실무기초] XLOOKUP 함수 실전예제 총정리
    예제파일
  • [실무기초] XLOOKUP 함수 실전예제 총정리
    완성파일

관련 기초함수

부서별 상여금 계산하기 (왼쪽방향 조회, N/A값 처리)

왼쪽방향 검색

XLOOKUP 함수는 조회범위의 왼쪽방향으로 값을 검색할 수 있습니다. 따라서 아래 그림과 같이, 출력범위인 [상여금%]이 조회범위인 [부서명]에 왼쪽에 있더라도 특정부서의 [상여금%]를 간단히 조회할 수 있습니다.

xlookup 출력범위 왼쪽방향 위치
XLOOKUP 함수는 조회범위의 왼쪽방향으로 값을 검색할 수 있습니다

예제파일 '상여금 및 소득세계산' 시트의 E5셀에 아래 수식을 붙여넣기 한 뒤, 자동채우기합니다.

=XLOOKUP(해당부서, $부서명범위, $상여금범위)
=XLOOKUP(B5,$N$5:$N$9,$M$5:$M$9)
XLOOKUP 함수 왼쪽방향 조회 상여금 계산
XLOOKUP 함수를 위와 같이 입력합니다.
N/A값 처리하기

수식을 입력하면 해당 부서가 오른쪽 표에 존재하지 않을경우, 함수의 결과값으로 #N/A 오류가 출력됩니다. 기존 VLOOKUP 함수의 경우, #N/A 오류를 숨기기위해 IFERROR 함수 또는 IFNA 함수를 사용해야 했습니다.

하지만 XLOOKUP 함수는 3번째 인수인 [N/A값]을 입력해서 #N/A 오류를 다른값으로 쉽게 변경할 수 있습니다. 이번 예제에서는 누락된 부서의 상여금비율이 '0%' 가 되도록 수식을 입력하겠습니다.

=XLOOKUP(B5,$N$5:$N$9,$M$5:$M$9, 0)

XLOOKUP함수 상여금 계산
[N/A값] 인수를 입력해서 #N/A 오류를 0%로 대체합니다.

이제 각 직원별 상여금 및 급여합계 계산하여 마무리합니다.

상여금 = 기본급 * 상여금비율
급여합계 = 기본급 + 상여금
상여금 및 급여합계 수식
직원별 상여금 및 급여합계를 계산합니다.

급여 소득세율 및 누진공제액 계산 (유사일치 검색)

예제파일 '상여금 및 소득세계산' 시트의 G:J 열을 선택한 후, 숨기기취소를 합니다. '세율' 및 누진공제'열이 나타납니다.

세율 및 누진공제 열 숨기기 취소
G:J 열을 '숨기기취소' 하면 '세율' 및 '누진공제'열이 나타납니다

우측의 2020년 소득세율표에서, 과세표준(최소) 범위를 기준으로 각 직원의 소득세율 및 누진공제액을 계산합니다.

과세표준 최소 범위 기준 조회
'과세표준(최소)' 범위를 기준으로 값을 조회합니다

XLOOKUP 함수 검색옵션의 기본값은 '정확히일치' 이며, 검색옵션으로 '같거나 작은값', '같거나 큰값' 또는 '와일드카드 문자 부분일치'로 검색할 수 있습니다.

XLOOKUP 함수 일치옵션 정확히 일치하거나 작은 항목
XLOOKUP 함수는 다양한 방법의 유사일치 검색을 지원합니다

본 예제에서는 '과세표준(최소)' 범위에서 해당 직원의 급여보다 '작거나 같은' 조건으로 검색합니다.

세율을 구하는 XLOOKUP 함수 공식

=XLOOKUP (급여, $과세표준(최소)범위, $세율범위,,-1)
=XLOOKUP (G5,$P$5:$P$11,$R$5:$R$11,,-1)

누진공제액을 구하는 XLOOKUP 함수 공식

=XLOOKUP (급여, $과세표준(최소)범위, $누진공제범위,,-1)
=XLOOKUP (G5,$P$5:$P$11,$S$5:$S$11,,-1)
XLOOKUP 함수 세율 및 누진공제 계산 완료
각 직원의 세율 및 누진공제액을 계산합니다

아래 수식으로 직원별 소득세 계산을 마무리합니다.

소득세 = (급여합계 * 세율) - 누진공제액
직원별 소득세 계산 공식
각 세율과 누진공제액을 바탕으로 직원별 소득세를 계산합니다

고객의 마지막 방문일 찾기 (양방향 검색)

기존 VLOOKUP 함수는 조회범위의 위에서부터 값을 검색했습니다. 따라서 조회범위의 아래에서부터 역방향으로 값을 검색해야 할 경우, LOOKUP 함수를 응용한 공식을 사용해야 했는데요.

XLOOKUP 함수는 조회범위에서 정방향 및 역방향으로 양방향 검색이 가능합니다. 기본값은 정방향(위에서 아래방향) 검색이며, 마지막 인수인 search_mode를 '-1'로 입력하면 역방향 검색이 가능합니다.

XLOOKUP 함수 검색방향
XLOOKUP 함수를 사용하면 양방향 검색이 가능합니다

이러한 특성을 활용하면 특정 고객의 마지막 방문일을 아주 쉽게 검색할 수 있습니다. 예제파일 '양방향&와일드카드' 시트로 이동한 뒤, H5셀에 아래 수식을 입력합니다.

=XLOOKUP (고객ID, $고객ID범위, $날짜범위, , , -1)
=XLOOKUP (G5, $C:$C, $B:$B, , , -1)

ID셀에 고객 ID를 입력하면 해당 고객의 마지막방문일을 조회할 수 있습니다.

엑셀 XLOOKUP 양방향 검색 GIF
선택한 고객의 마지막방문일을 조회합니다

일부 문자열로 고객 ID 찾기 (부분일치 검색)

XLOOKUP 함수는 와일드카드를 사용한 부분일치 검색도 가능합니다. 5번째 인수인 match_mode에 '2'를 입력하면 '와일드카드 문자 일치' 검색이 가능합니다.

XLOOKUP함수 일치 옵션 와일드카드 부분일치
와일드카드를 사용하여 부분일치 검색을 할 수 있습니다

이 기능을 활용하면 고객이 ID를 잊어버렸을경우, 고객 ID 일부만으로 해당 고객의 ID를 찾는 수식을 작성할 수 있습니다. '양방향&와일드카드' 시트 H9셀에 아래 수식을 입력합니다.

=XLOOKUP ("*" & 고객ID & "*", $고객ID범위, $고객ID범위, , 2)
=XLOOKUP("*"&G9&"*", $C:$C, $C:$C , , 2)

고객 ID의 일부만 입력하면 해당 문자가 포함된 고객 ID가 반환됩니다.

엑셀 XLOOKUP 와일드카드 검색 GIF
일부 문자열을 포함하는 고객 ID를 검색합니다

여러필드 동시 출력하기 (동적배열함수)

기존 VLOOKUP 함수는 '하나의 열'만 결과값으로 출력할 수 있었습니다. 따라서 여러개의 필드를 동시에 출력하려면, 각 필드마다 함수를 따로 입력해줘야 했습니다.

VLOOKUP 함수 모든 필드마다 함수 입력
VLOOKUP 함수는 각 필드마다 하나씩 함수를 입력해야 합니다

하지만 XLOOKUP 함수는 '동적배열함수'이므로, 출력할 범위를 넓게 지정해서 해당 범위의 모든 결과값을 한번에 반환할 수 있습니다.

XLOOKUP함수 출력범위 모든 값 한번에 반환
XLOOKUP 함수는 여러필드의 값을 한번에 반환할 수 있습니다

예제파일 '범위반환 및 결과값합치기' 시트의 I5셀에 아래 수식을 입력합니다.

=XLOOKUP (직원ID, $직원ID범위, $직원정보전체범위 )
=XLOOKUP (H5, $B$5:$B$22, $C$5:$F$22)

단, 주의사항으로 값이 출력될 범위(엑셀에서는 '분산 범위'라고 합니다.)가 비어있지 않을경우, XLOOKUP 함수는 #SPILL! 오류를 반환합니다.

XLOOKUP 함수 SPILL 오류
출력될 범위가 비어있지 않을경우, #SPILL! 오류를 반환합니다

모든 출력값을 하나의 문자열로 합치기 (TEXTJOIN함수 응용)

XLOOKUP 함수는 결과값으로 '범위(배열)'을 반환합니다. 따라서 다른 여러 함수와 접목하여 다양한 방법으로 응용할 수가 있습니다.

예를들어 TEXTJOIN 함수를 응용하면, XLOOKUP 함수로 반환된 모든 출력값을 하나의 문자열로 합칠수가 있습니다.

엑셀 XLOOKUP함수 결과값 합치기
출력된 모든 필드를 하나의 문자열로 합칠 수 있습니다

예제파일 '범위반환 및 결과값합치기' 시트 I10셀에 아래 수식을 입력합니다.

=TEXTJOIN(" / ",TRUE,XLOOKUP(H10,$B$5:$B$22,$C$5:$F$22))
XLOOKUP 함수 TEXTJOIN 함수 응용 GIF
모든 직원정보가 한 문장으로 출력됩니다

특정직원의 분기별 최다 판매량 찾기 (MAX함수 응용)

뿐만아니라, XLOOKUP 함수와 MAX 함수를 같이 응용할 수도 있습니다. 아래 그림처럼, 각 직원의 분기별 판매실적이 정리된 표에서, 특정직원의 분기별 최다 판매실적을 구해보겠습니다.

XLOOKUP 함수 특정 조건 최대값 구하기
특정직원의 분기별 최다 판매실적을 구합니다

예제파일 '특정조건의 최대값 찾기' 시트의 I5셀에 아래 수식을 입력합니다.

=MAX(XLOOKUP(H5,$B$5:$B$22,$C$5:$F$22))
XLOOKUP 함수 MAX 함수 응용 최대값 구하기 GIF
선택한 직원의 분기별 최다판매량을 출력합니다

링크 : MS 홈페이지 XLOOKUP 상세설명

4.8 8 투표
게시글평점
guest
11 댓글
Inline Feedbacks
모든 댓글 보기
Netpiapro
Netpiapro(@netpiapro)
2020년 3월 26일 5:41 오후
게시글평점 :
     

E-Book은 언제쯤 올라 올까요??? 동강보단 문서 보면서 공부하는게 더 편한 1인 입니다.

소금인형
소금인형(@1354-2)
2020년 3월 28일 4:34 오후
게시글평점 :
     

조건함수의 진화...

크리슈
크리슈(@1373)
2020년 3월 29일 8:53 오후
게시글평점 :
     

엑스룩업 덕분에 정말 세상이 편해졌습니다..ㅎ 감사해요!

노은
노은(@1365)
2020년 4월 3일 11:43 오후
게시글평점 :
     

감사합니다. 실무 마지막까지 보았네요

날으는공
날으는공(@1484)
2020년 4월 17일 10:00 오전
답글 남기기  노은

엑스룩업 함수의 대단합니다. 그런데 엑스룩업 실행하면 도움말이 안나와요.

엑린이
엑린이(@1125)
2020년 4월 22일 12:13 오전
게시글평점 :
     

얼른 배워서 야근을 줄이고 싶네요 ㅎ

정
(@heajung_jo1naver-com)
2021년 1월 7일 10:17 오전
게시글평점 :
     

X-LOOKUP 완전 편리! 유용한 강의 감사합니다.

배기우
배기우(@mazamarome-com)
2021년 2월 11일 7:32 오후
게시글평점 :
     

복 받으실거예요~

김치찌개
김치찌개(@24prayernaver-com)
2021년 10월 21일 9:57 오전
게시글평점 :
     

안녕하세요? Index & Match 조합을 Xlookup으로 대체하고 싶은 1인입니다. 그런데 Xlookup에서 특정 한 열을 검색해서 그 열의 모든 영역을 참조영역으로 지정할 수 있는 방법은 없을까요? Row는 지정이 가능한데 여러 컬럼… 더보기 »

11
0
여러분의 생각을 댓글로 남겨주세요.x