엑셀 신규함수, XLOOKUP 함수 실전예제 및 응용방법 총정리
XLOOKUP 함수 예제 목차 바로가기
강의요약
2020년 2월 10일자로 오피스 365 월단위채널(공식 채널)에 XLOOKUP 함수가 업데이트 되었습니다. XLOOKUP 함수는 이번에 업데이트 된 동적배열함수 가장 중요한 함수로서, 이제 실무자라면 반드시 알아야 할 필수함수가 되었습니다.
이번 강의에서는 XLOOKUP 함수의 실전예제 및 동적배열함수의 특성을 이용한 응용방법 2가지를 각 단계별로 알아보겠습니다.
엑셀 XLOOKUP 함수는 20년 2월 기준, 오피스 365 구독자에게만 공개된 함수이므로 엑셀 2019 버전을 포함한 기존의 설치형 엑셀 사용자는 사용이 불가합니다.
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] XLOOKUP 함수 실전예제 총정리예제파일[실무기초] XLOOKUP 함수 실전예제 총정리완성파일
관련 기초함수
왼쪽방향 검색
XLOOKUP 함수는 조회범위의 왼쪽방향으로 값을 검색할 수 있습니다. 따라서 아래 그림과 같이, 출력범위인 [상여금%]이 조회범위인 [부서명]에 왼쪽에 있더라도 특정부서의 [상여금%]를 간단히 조회할 수 있습니다.
XLOOKUP 함수는 조회범위의 왼쪽방향으로 값을 검색할 수 있습니다 예제파일 '상여금 및 소득세계산' 시트의 E5셀에 아래 수식을 붙여넣기 한 뒤, 자동채우기합니다.
=XLOOKUP(해당부서, $부서명범위, $상여금범위)
=XLOOKUP(B5,$N$5:$N$9,$M$5:$M$9)
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)
[N/A값] 인수를 입력해서 #N/A 오류를 0%로 대체합니다.
이제 각 직원별 상여금 및 급여합계 계산하여 마무리합니다.상여금 = 기본급 * 상여금비율
급여합계 = 기본급 + 상여금
직원별 상여금 및 급여합계를 계산합니다. 예제파일 '상여금 및 소득세계산' 시트의 G:J 열을 선택한 후, 숨기기취소를 합니다. '세율' 및 누진공제'열이 나타납니다.
G:J 열을 '숨기기취소' 하면 '세율' 및 '누진공제'열이 나타납니다 우측의 2020년 소득세율표에서, 과세표준(최소) 범위를 기준으로 각 직원의 소득세율 및 누진공제액을 계산합니다.
'과세표준(최소)' 범위를 기준으로 값을 조회합니다 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)
각 직원의 세율 및 누진공제액을 계산합니다 아래 수식으로 직원별 소득세 계산을 마무리합니다.
소득세 = (급여합계 * 세율) - 누진공제액
각 세율과 누진공제액을 바탕으로 직원별 소득세를 계산합니다 기존 VLOOKUP 함수는 조회범위의 위에서부터 값을 검색했습니다. 따라서 조회범위의 아래에서부터 역방향으로 값을 검색해야 할 경우, LOOKUP 함수를 응용한 공식을 사용해야 했는데요.
XLOOKUP 함수는 조회범위에서 정방향 및 역방향으로 양방향 검색이 가능합니다. 기본값은 정방향(위에서 아래방향) 검색이며, 마지막 인수인 search_mode를 '-1'로 입력하면 역방향 검색이 가능합니다.
XLOOKUP 함수를 사용하면 양방향 검색이 가능합니다 이러한 특성을 활용하면 특정 고객의 마지막 방문일을 아주 쉽게 검색할 수 있습니다. 예제파일 '양방향&와일드카드' 시트로 이동한 뒤, H5셀에 아래 수식을 입력합니다.
=XLOOKUP (고객ID, $고객ID범위, $날짜범위, , , -1)
=XLOOKUP (G5, $C:$C, $B:$B, , , -1)ID셀에 고객 ID를 입력하면 해당 고객의 마지막방문일을 조회할 수 있습니다.
선택한 고객의 마지막방문일을 조회합니다 XLOOKUP 함수는 와일드카드를 사용한 부분일치 검색도 가능합니다. 5번째 인수인 match_mode에 '2'를 입력하면 '와일드카드 문자 일치' 검색이 가능합니다.
와일드카드를 사용하여 부분일치 검색을 할 수 있습니다 이 기능을 활용하면 고객이 ID를 잊어버렸을경우, 고객 ID 일부만으로 해당 고객의 ID를 찾는 수식을 작성할 수 있습니다. '양방향&와일드카드' 시트 H9셀에 아래 수식을 입력합니다.
=XLOOKUP ("*" & 고객ID & "*", $고객ID범위, $고객ID범위, , 2)
=XLOOKUP("*"&G9&"*", $C:$C, $C:$C , , 2)고객 ID의 일부만 입력하면 해당 문자가 포함된 고객 ID가 반환됩니다.
일부 문자열을 포함하는 고객 ID를 검색합니다 기존 VLOOKUP 함수는 '하나의 열'만 결과값으로 출력할 수 있었습니다. 따라서 여러개의 필드를 동시에 출력하려면, 각 필드마다 함수를 따로 입력해줘야 했습니다.
VLOOKUP 함수는 각 필드마다 하나씩 함수를 입력해야 합니다 하지만 XLOOKUP 함수는 '동적배열함수'이므로, 출력할 범위를 넓게 지정해서 해당 범위의 모든 결과값을 한번에 반환할 수 있습니다.
XLOOKUP 함수는 여러필드의 값을 한번에 반환할 수 있습니다 예제파일 '범위반환 및 결과값합치기' 시트의 I5셀에 아래 수식을 입력합니다.
=XLOOKUP (직원ID, $직원ID범위, $직원정보전체범위 )
=XLOOKUP (H5, $B$5:$B$22, $C$5:$F$22)단, 주의사항으로 값이 출력될 범위(엑셀에서는 '분산 범위'라고 합니다.)가 비어있지 않을경우, XLOOKUP 함수는 #SPILL! 오류를 반환합니다.
출력될 범위가 비어있지 않을경우, #SPILL! 오류를 반환합니다 XLOOKUP 함수는 결과값으로 '범위(배열)'을 반환합니다. 따라서 다른 여러 함수와 접목하여 다양한 방법으로 응용할 수가 있습니다.
예를들어 TEXTJOIN 함수를 응용하면, XLOOKUP 함수로 반환된 모든 출력값을 하나의 문자열로 합칠수가 있습니다.
출력된 모든 필드를 하나의 문자열로 합칠 수 있습니다 예제파일 '범위반환 및 결과값합치기' 시트 I10셀에 아래 수식을 입력합니다.
=TEXTJOIN(" / ",TRUE,XLOOKUP(H10,$B$5:$B$22,$C$5:$F$22))
모든 직원정보가 한 문장으로 출력됩니다 뿐만아니라, XLOOKUP 함수와 MAX 함수를 같이 응용할 수도 있습니다. 아래 그림처럼, 각 직원의 분기별 판매실적이 정리된 표에서, 특정직원의 분기별 최다 판매실적을 구해보겠습니다.
특정직원의 분기별 최다 판매실적을 구합니다 예제파일 '특정조건의 최대값 찾기' 시트의 I5셀에 아래 수식을 입력합니다.
=MAX(XLOOKUP(H5,$B$5:$B$22,$C$5:$F$22))
선택한 직원의 분기별 최다판매량을 출력합니다
