엑셀의 새로운 기능인 XLOOKUP은 기존의 VLOOKUP을 대체하며, 엑셀의 가장 중요한 기능 중 하나를 혁신적으로 개선합니다. 이 새로운 함수는 VLOOKUP의 한계를 극복하고 다양한 추가 기능을 제공하여 사용자에게 더 강력하고 유연한 데이터 조회 경험을 선사합니다. XLOOKUP이 무엇이며, 어떻게 활용할 수 있는지 자세히 알아보겠습니다.
XLOOKUP이란 무엇인가?
XLOOKUP 함수는 VLOOKUP의 몇 가지 주요 제약 사항을 해결하는 데 초점을 맞추어 개발되었습니다. 또한, HLOOKUP 함수까지 대체할 수 있는 강력한 기능입니다. 예를 들어, XLOOKUP은 검색 범위를 기준으로 왼쪽 값을 참조할 수 있으며, 기본적으로 정확히 일치하는 값을 찾습니다. 또한, 열 번호 대신 셀 범위를 직접 지정할 수 있어 사용자 편의성이 크게 향상되었습니다. VLOOKUP에 비해 사용법이 훨씬 더 직관적이고 다양합니다. XLOOKUP이 실제로 어떻게 작동하는지 자세히 살펴보겠습니다.
현재 XLOOKUP은 Microsoft Insider 프로그램 참가자만 이용할 수 있습니다. Insider 프로그램에 참여하면 최신 엑셀 기능을 빠르게 사용해 볼 수 있습니다. Microsoft는 조만간 모든 Office 365 사용자에게 이 기능을 배포할 예정입니다.
XLOOKUP 함수 사용법
XLOOKUP 함수의 실제 작동 방식을 이해하기 위해 예시를 살펴보겠습니다. 아래와 같은 데이터 테이블이 있다고 가정해 봅시다. A열의 각 ID에 대해 F열의 해당 부서를 찾아 반환하는 것이 목표입니다.
이것은 전형적인 완전 일치 조회의 예입니다. XLOOKUP 함수는 이 작업을 수행하기 위해 세 가지 핵심 정보만 필요로 합니다.
아래 이미지는 6개의 인수를 갖는 XLOOKUP 함수의 구조를 보여주지만, 정확히 일치하는 값을 찾을 때는 처음 3개의 인수만 필수입니다. 처음 3개 인수에 집중해서 알아봅시다.
Lookup_value: 찾고자 하는 값
Lookup_array: 찾을 값이 있는 범위
Return_array: 반환하고자 하는 값이 있는 범위
위 예시에서는 다음 수식이 작동합니다: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)
이제부터 XLOOKUP이 VLOOKUP에 비해 갖는 몇 가지 주요 장점을 살펴봅시다.
더 이상 열 인덱스 번호는 필요 없다
VLOOKUP에서 악명 높았던 세 번째 인수(반환할 정보가 있는 열 번호를 지정)는 XLOOKUP에서는 더 이상 필요하지 않습니다. XLOOKUP에서는 반환할 범위를 직접 선택할 수 있으므로 이와 같은 복잡한 열 번호 지정은 필요하지 않습니다. 위 예시에서는 F열을 선택하면 됩니다.
또한 중요한 점은, XLOOKUP은 VLOOKUP과 달리 선택한 셀의 왼쪽 데이터를 참조할 수 있다는 것입니다. 이에 대한 자세한 내용은 아래에서 더 자세히 다루겠습니다.
엑셀 시트에 새 열을 삽입할 때, 수식이 깨지는 문제가 더 이상 발생하지 않습니다. 스프레드시트의 데이터가 변경되면 XLOOKUP의 반환 범위는 자동으로 조정됩니다.
정확히 일치하는 값 찾기, 기본 설정
VLOOKUP을 처음 배울 때, 왜 정확한 일치를 지정해야 하는지 항상 혼란스러웠습니다. 이제 XLOOKUP은 기본적으로 정확히 일치하는 값을 찾도록 설정되어 있어 더욱 편리합니다. 이것은 조회 수식을 사용할 때 가장 일반적인 이유입니다. 다섯 번째 인수를 수동으로 설정할 필요가 없어 초보자도 쉽게 사용할 수 있으며 오류 발생 가능성이 줄어듭니다.
간단히 말해서, XLOOKUP은 VLOOKUP보다 더 적은 질문을 요구하며 사용자 친화적이고 내구성이 뛰어납니다.
XLOOKUP, 왼쪽 값도 참조 가능
검색 범위를 자유롭게 선택할 수 있다는 점은 XLOOKUP을 VLOOKUP보다 훨씬 더 유연하게 만듭니다. 테이블 열의 순서는 XLOOKUP에서 더 이상 중요하지 않습니다.
VLOOKUP은 테이블의 가장 왼쪽 열에서만 검색하고, 지정된 열 수만큼 오른쪽으로 이동하여 값을 반환해야 한다는 제약이 있었습니다.
아래 예에서는 ID(E열)를 기준으로 검색하여 해당 사람의 이름(D열)을 반환해야 합니다.
다음 수식으로 원하는 결과를 얻을 수 있습니다: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)
찾을 수 없는 값 처리 방법
조회 기능을 사용하는 사용자는 VLOOKUP 또는 MATCH 함수에서 찾고자 하는 값을 찾을 수 없을 때 나타나는 #N/A 오류 메시지에 익숙합니다. 종종 이 오류는 논리적인 이유로 발생하지만, 사용자는 오류가 정확하지 않거나 유용하지 않다고 느껴 빠르게 이 오류를 숨기는 방법을 찾아야만 했습니다.
XLOOKUP에는 “찾을 수 없는 경우” 인수가 내장되어 있어 이런 오류를 보다 쉽게 처리할 수 있습니다. 이전 예시에서 ID가 잘못 입력된 경우를 가정해 봅시다.
다음 수식은 오류 메시지 대신 “잘못된 ID”라는 텍스트를 표시합니다. =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,”잘못된 ID”)
XLOOKUP을 이용한 범위 조회
정확한 일치만큼 일반적이지는 않지만, 조회 수식의 매우 효과적인 활용 사례는 범위 내에서 값을 찾는 것입니다. 예를 들어, 지출 금액에 따라 할인율을 반환하고 싶다고 가정해 봅시다.
이번에는 특정 값을 검색하는 것이 아니라, B열의 값이 E열의 범위에 속하는 위치를 찾아 해당 할인율을 결정해야 합니다.
XLOOKUP에는 일치 모드라는 선택적 다섯 번째 인수가 있으며, 기본 설정은 정확히 일치하는 값을 찾는 것입니다.
XLOOKUP은 VLOOKUP에 비해 근사값 일치 기능이 더욱 강력합니다.
(-1)보다 작거나 (1)보다 큰 가장 가까운 일치를 찾는 옵션을 제공합니다. 또한 ? 와 * 와 같은 와일드카드 문자(2)를 사용하는 옵션도 있습니다. 이러한 설정은 VLOOKUP처럼 기본적으로 켜져 있지 않습니다.
이 예시의 수식은 정확히 일치하는 항목이 없을 경우 찾은 값보다 작은 가장 가까운 값을 반환합니다. =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)
그러나 C7 셀에는 #N/A 오류가 반환됩니다.(‘찾을 수 없는 경우’ 인수가 사용되지 않았기 때문입니다.) 지출액 64는 할인 기준에 도달하지 않았기 때문에 0% 할인이 반환되어야 합니다.
XLOOKUP 함수의 또 다른 장점은, VLOOKUP과 달리 조회 범위가 오름차순으로 정렬될 필요가 없다는 것입니다.
조회 테이블의 하단에 새 행을 입력한 다음 수식을 열고, 모서리를 클릭하고 끌어 사용 범위를 확장해 보세요.
수식은 즉시 오류를 수정합니다. 범위의 맨 아래에 “0”이 있는 것은 문제가 되지 않습니다.
개인적으로는 여전히 조회 열을 기준으로 테이블을 정렬하는 것을 선호하지만, 맨 아래에 “0”이 있는 것은 문제가 될 수 있습니다. 하지만 공식이 깨지지 않는다는 점은 정말 편리합니다.
XLOOKUP, HLOOKUP 기능 대체
앞서 언급했듯이 XLOOKUP 함수는 HLOOKUP을 대체하기 위해 개발되었습니다. 즉, 두 가지 기능을 하나의 함수로 통합한 것입니다. 정말 멋지지 않나요!
HLOOKUP 함수는 행을 따라 값을 검색하는 데 사용되는 수평 조회 함수입니다.
VLOOKUP만큼 널리 알려져 있지는 않지만, 아래 예와 같이 헤더가 A열에 있고 데이터가 4행과 5행에 있는 상황에서 유용하게 사용할 수 있습니다.
XLOOKUP은 아래쪽 열과 행을 따라 양방향으로 검색할 수 있어, 더 이상 두 개의 서로 다른 함수를 사용할 필요가 없습니다.
이 예시에서는 A2 셀의 이름과 관련된 판매 값을 반환하기 위해 다음 수식이 사용됩니다. 4행을 따라 이름을 찾고, 5행에서 해당 값을 반환합니다. =XLOOKUP(A2,B4:E4,B5:E5)
XLOOKUP, 하향식 검색 기능
일반적으로 값의 첫 번째(또는 유일한) 발생을 찾기 위해 목록을 검색해야 합니다. XLOOKUP에는 검색 모드라는 여섯 번째 인수가 있으며, 이 인수를 활용하여 조회를 목록의 맨 아래에서 시작하고, 위쪽으로 검색하여 마지막 항목의 값을 찾을 수 있습니다.
아래 예시에서는 A열의 각 제품에 대한 재고 수준을 찾아야 합니다.
조회 테이블은 날짜 순서로 정렬되어 있으며, 각 제품에 대해 여러 번의 재고 확인이 있습니다. 여기서 우리는 마지막으로 확인된 시점(제품 ID의 마지막 항목)의 재고 수준을 반환하고자 합니다.
XLOOKUP 함수의 여섯 번째 인수는 네 가지 옵션을 제공합니다. 여기서는 “마지막에서 먼저 검색” 옵션을 사용해야 합니다.
완성된 수식은 다음과 같습니다: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)
이 수식에서 네 번째 및 다섯 번째 인수는 생략되었습니다. 이들은 선택적 인수로, 정확히 일치하는 값을 찾기 위해서는 기본값을 사용하면 됩니다.
마무리
XLOOKUP 함수는 VLOOKUP 및 HLOOKUP 함수를 대체하기 위해 오랫동안 기다려온 기능입니다.
공식 발표
본 문서에서는 XLOOKUP의 다양한 장점을 보여주기 위해 여러 가지 예시를 사용했습니다. XLOOKUP은 시트, 통합 문서 및 테이블 등 엑셀의 다양한 영역에서 활용 가능합니다. 예시는 이해를 돕기 위해 최대한 간단하게 구성되었습니다.
엑셀에 도입되는 동적 배열 기능 덕분에 곧 값 범위를 반환할 수도 있을 것입니다. 이 부분은 향후 더 자세히 알아볼 가치가 있습니다.
VLOOKUP의 시대는 저물고 있습니다. XLOOKUP의 등장으로 곧 조회 함수는 XLOOKUP이 대세가 될 것입니다.