XLOOKUP 함수 실전예제 및 응용방법
오피스 365 신규 함수인 XLOOKUP의 실전예제 및 고급 응용방법
이 강의에서는 오피스 365에 새롭게 추가된 XLOOKUP 함수의 실전 활용법을 다룹니다. VLOOKUP의 한계였던 왼쪽 방향 검색과 #N/A 오류 처리부터, 양방향·와일드카드 검색과 동적 배열을 활용한 다중 필드 동시 출력까지 7가지 예제로 단계별로 정리합니다.
실습자료를 준비했어요
수업에서 사용한 예제 파일과 보충 자료를 한 곳에 정리했습니다!👇
강의 요약
2020년 2월 10일, 오피스 365 월 단위 채널(공식 채널)에 XLOOKUP 함수가 업데이트되었습니다. XLOOKUP은 이번에 함께 추가된 동적 배열 함수 가운데 가장 중요한 함수로, 이제 실무자라면 반드시 익혀 두어야 할 필수 함수가 되었습니다.
이번 강의에서는 XLOOKUP 함수의 실전 예제와 함께, 동적 배열 함수의 특성을 활용한 두 가지 응용 방법을 단계별로 살펴보겠습니다.
XLOOKUP 함수는 2020년 2월 기준 오피스 365 구독자에게만 공개된 함수입니다. 따라서 엑셀 2019 버전을 포함한 기존 설치형 엑셀 사용자는 사용할 수 없습니다.
영상 강의
관련 기초 함수
왼쪽 방향 검색
XLOOKUP 함수는 조회 범위의 왼쪽 방향으로 값을 검색할 수 있습니다. 따라서 아래 그림과 같이 출력 범위인 [상여금%]이 조회 범위인 [부서명]보다 왼쪽에 있더라도, 특정 부서의 [상여금%]를 간단하게 조회할 수 있습니다.
예제파일 '상여금 및 소득세계산' 시트의 E5셀에 아래 수식을 붙여넣은 뒤 자동 채우기를 실행합니다.
=XLOOKUP(B5,$N$5:$N$9,$M$5:$M$9)
#N/A 오류 처리하기
수식을 입력하면 해당 부서가 오른쪽 표에 존재하지 않을 때 함수의 결과값으로 #N/A 오류가 출력됩니다. 기존 VLOOKUP 함수에서는 이러한 #N/A 오류를 숨기기 위해 IFERROR 함수 또는 IFNA 함수를 별도로 사용해야 했습니다.
반면 XLOOKUP 함수는 3번째 인수 [N/A값]을 입력하여 #N/A 오류를 다른 값으로 손쉽게 대체할 수 있습니다. 이번 예제에서는 누락된 부서의 상여금 비율이 '0%'가 되도록 수식을 작성하겠습니다.
이제 각 직원별 상여금과 급여 합계를 계산하여 마무리합니다.
급여합계 = 기본급 + 상여금
예제파일 '상여금 및 소득세계산' 시트의 G:J 열을 선택한 후 [숨기기 취소]를 실행합니다. '세율'과 '누진공제' 열이 다시 나타납니다.
오른쪽의 2020년 소득세율표에서, 과세표준(최소) 범위를 기준으로 각 직원의 소득세율과 누진공제액을 계산합니다.
XLOOKUP 함수의 검색 옵션 기본값은 '정확히 일치'이며, 검색 옵션을 통해 '같거나 작은 값', '같거나 큰 값', '와일드카드 문자 부분일치' 등 다양한 방식으로 검색할 수 있습니다.
이번 예제에서는 '과세표준(최소)' 범위에서 해당 직원의 급여보다 '작거나 같은' 조건으로 검색합니다.
세율을 구하는 XLOOKUP 함수 공식
=XLOOKUP (G5,$P$5:$P$11,$R$5:$R$11,,-1)
누진공제액을 구하는 XLOOKUP 함수 공식
=XLOOKUP (G5,$P$5:$P$11,$S$5:$S$11,,-1)
아래 수식으로 직원별 소득세 계산을 마무리합니다.
기존 VLOOKUP 함수는 조회 범위의 위에서부터 값을 검색했습니다. 따라서 조회 범위의 아래에서부터 역방향으로 값을 찾으려면 LOOKUP 함수를 응용한 별도의 공식을 사용해야 했습니다.
반면 XLOOKUP 함수는 조회 범위에서 정방향과 역방향 모두 양방향 검색이 가능합니다. 기본값은 정방향(위에서 아래) 검색이며, 마지막 인수인 search_mode를 '-1'로 입력하면 역방향 검색이 가능합니다.
이러한 특성을 활용하면 특정 고객의 마지막 방문일을 매우 간단하게 검색할 수 있습니다. 예제파일 '양방향&와일드카드' 시트로 이동한 뒤, H5셀에 아래 수식을 입력합니다.
=XLOOKUP (G5, $C:$C, $B:$B, , , -1)
ID셀에 고객 ID를 입력하면 해당 고객의 마지막 방문일이 즉시 조회됩니다.
XLOOKUP 함수는 와일드카드를 사용한 부분일치 검색도 지원합니다. 5번째 인수인 match_mode에 '2'를 입력하면 '와일드카드 문자 일치' 검색이 가능합니다.
이 기능을 활용하면 고객이 자신의 ID를 잊어버린 경우, 고객 ID 일부만으로 해당 고객의 전체 ID를 찾는 수식을 작성할 수 있습니다. '양방향&와일드카드' 시트의 H9셀에 아래 수식을 입력합니다.
=XLOOKUP("*"&G9&"*", $C:$C, $C:$C , , 2)
고객 ID의 일부만 입력하면 해당 문자가 포함된 전체 고객 ID가 반환됩니다.
기존 VLOOKUP 함수는 '하나의 열'만 결과값으로 출력할 수 있었습니다. 따라서 여러 개의 필드를 동시에 출력하려면 각 필드마다 함수를 따로 입력해야 했습니다.
반면 XLOOKUP 함수는 '동적 배열 함수'이므로, 출력 범위를 넓게 지정하면 해당 범위의 모든 결과값을 한 번에 반환할 수 있습니다.
예제파일 '범위반환 및 결과값합치기' 시트의 I5셀에 아래 수식을 입력합니다.
=XLOOKUP (H5, $B$5:$B$22, $C$5:$F$22)
다만 주의할 점이 있습니다. 값이 출력될 범위(엑셀에서는 '분산 범위'라고 합니다)가 비어 있지 않을 경우, XLOOKUP 함수는 #SPILL! 오류를 반환합니다.
XLOOKUP 함수는 결과값으로 '범위(배열)'을 반환합니다. 따라서 다른 여러 함수와 접목하면 매우 다양한 방식으로 응용할 수 있습니다.
예를 들어 TEXTJOIN 함수를 응용하면, XLOOKUP 함수로 반환된 모든 출력값을 하나의 문자열로 합칠 수 있습니다.
예제파일 '범위반환 및 결과값합치기' 시트의 I10셀에 아래 수식을 입력합니다.
뿐만 아니라 XLOOKUP 함수는 MAX 함수와도 함께 응용할 수 있습니다. 아래 그림처럼 각 직원의 분기별 판매실적이 정리된 표에서, 특정 직원의 분기별 최다 판매실적을 구해 보겠습니다.
예제파일 '특정조건의 최대값 찾기' 시트의 I5셀에 아래 수식을 입력합니다.
홈페이지 특성상 종종 업데이트가 일어나는데, E-Book은 관리상의 어려움이 있어 현재는 포스트를 더욱 깔끔하게 작성하는데 집중하고 있습니다.
페이지를 우클릭 하신뒤, '인쇄' - 'PDF 출력'을 선택해서 PDF형식으로 웹페이지를 다운받아 보시겠어요?
감사합니다.
Row는 지정이 가능한데 여러 컬럼 중 특정 컬럼에 해당하는 영역을 배열로 반환하고 싶습니다.
설명이 충분한지 모르겠네요 ^^;
XLOOKUP은 기본으로 배열을 결과로 반환합니다.
출력범위로 넓게 (예: B:E)를 넣어주면, 찾을값과 일치하는 곳의 배열이 동적배열(파란색 범위)로 반환됩니다.
강의 마지막에 소개해드린 방법을 한번 확인해보시겠어요? =) 다른 함수와 같이 응용할 수도 있습니다 ^^
감사합니다.
엑셀 함수를 사용하려면 csv 파일을
다른 이름으로 저장으로 xlsx 형태로 변환 후 다시 실행해보시겠어요?
csv 형태로 파일을 저장하면, 사용했던 함수, 피벗테이블이 사라지거나 #N/A 오류가 발생합니다. :)
감사합니다.
해당 오류는 해결했으나, 일자별로 추가 되는 데이터에 대해 기존 로우 데이터에 신규 데이터 추가 후 피벗테이블 새로고침을 눌렀는데
같은 행레이블 테이블이 새로운 테이블로 인식해 데이터가 쌓이고 있습니다.
어떻게 해결해야 할까요?
ex)
엘라비아(행레이블 기존 로우 데이터)
엘라비아(신규추가 로우데이터)