전통적인 VLOOKUP과 HLOOKUP으로 조회 함수의 기본을 다지고, INDEX와 MATCH 조합으로 VLOOKUP의 한계를 뛰어넘는 유연성을 경험했습니다. 이제 엑셀 조회 함수의 새로운 시대를 연, 혁신적인 XLOOKUP 함수를 마스터할 차례입니다!
엑셀 2019 및 Microsoft 365 사용자라면 이 함수 하나로 기존의 조회 함수들을 거의 대체할 수 있을 만큼 강력하고 편리합니다.
XLOOKUP이 어떻게 VLOOKUP과 INDEX/MATCH의 장점을 통합하고 새로운 기능을 추가하여 조회 함수의 새로운 표준으로 자리 잡았는지 함께 알아봅시다.
왜 XLOOKUP을 마스터해야 할까요?
VLOOKUP은 배우기 쉽지만, 왼쪽 검색 불가, 열 번호 고정, 기본 오류 처리의 한계 등 여러 단점이 있었습니다.
INDEX/MATCH는 이러한 단점을 보완했지만, 두 함수를 결합해야 하는 복잡성과 긴 수식 때문에 초보자에게는 진입 장벽이 높았죠.
XLOOKUP은 이 두 함수의 장점을 모두 흡수하고, 사용자 친화적인 인터페이스와 강력한 추가 기능들을 제공합니다.
- 좌우/상하 검색 자유로움: 검색할 열/행과 반환할 열/행을 각각 지정하여 VLOOKUP의 방향 제약을 없앴습니다.
- 간결한 구문: INDEX/MATCH보다 훨씬 짧고 직관적인 수식을 작성할 수 있습니다.
- 기본 오류 처리: 검색 결과가 없을 때 #N/A 오류 대신 원하는 메시지를 직접 지정할 수 있습니다.
- 역방향 검색: 목록의 마지막부터 처음까지 검색할 수 있습니다.
- 여러 값 반환: 조건에 맞는 여러 개의 값을 한 번에 반환할 수 있습니다 (동적 배열).
XLOOKUP은 현대 엑셀 사용자의 생산성을 폭발적으로 향상시키는 '게임 체인저'입니다.
주요 함수들
다음 함수들을 실무 예제와 함께 자세히 살펴보겠습니다.
- XLOOKUP: 엑셀의 현대적 조회 함수
- XMATCH: MATCH 함수의 현대적이고 유연한 버전
실무 예제로 배우는 엑셀 함수 마스터하기
각 함수를 실제 비즈니스 시나리오에 적용하여 XLOOKUP의 강력함과 편리함을 경험해 봅시다.
1. XLOOKUP: 조회 함수의 새로운 표준
XLOOKUP 함수는 지정된 값을 검색 범위에서 찾아 해당 값과 같은 위치에 있는 반환 범위의 값을 가져옵니다.
- 기본 형식: =XLOOKUP(찾을_값, 찾을_범위, 반환할_범위, [찾을_값이_없을_경우_값], [일치_모드], [검색_모드])
- 찾을_값: 검색하려는 값.
- 찾을_범위: 찾을_값을 검색할 단일 행 또는 단일 열 범위.
- 반환할_범위: 찾을_값이 있는 위치와 동일한 위치에서 값을 가져올 단일 행 또는 단일 열 범위.
- 찾을_값이_없을_경우_값 (선택 사항): 찾을_값을 찾지 못했을 때 반환할 값 (예: "정보 없음", "" 등). 생략하면 #N/A 반환.
- 일치_모드 (선택 사항):
- 0 (기본값): 정확히 일치.
- -1: 정확히 일치하는 값을 찾지 못하면 다음으로 작은 항목 반환.
- 1: 정확히 일치하는 값을 찾지 못하면 다음으로 큰 항목 반환.
- 2: 와일드카드 문자(?, *)를 사용하여 일치.
- 검색_모드 (선택 사항):
- 1 (기본값): 첫 번째 일치 항목부터 처음부터 끝까지 검색.
- -1: 마지막 일치 항목부터 끝에서부터 처음까지 검색.
- 2: 오름차순으로 정렬된 찾을_범위에서 이진 검색 (빠른 검색).
- -2: 내림차순으로 정렬된 찾을_범위에서 이진 검색 (빠른 검색).
실무 예제 1 (좌우 검색 자유로움): 제품명으로 제품 코드를 조회하거나, 제품 코드로 제품명을 조회해야 합니다.
| A | B | C |
| 제품 코드 | 제품명 | 단가 |
| P001 | 노트북 | 10,000 |
| P002 | 태블릿 | 15,000 |
| P003 | 스마트폰 | 8,000 |
예제 목표 1: '태블릿'의 제품 코드를 조회하세요. (오른쪽에서 왼쪽으로 검색)
해결 방법 1 (제품명으로 제품 코드 조회): E2 셀에 다음 수식을 입력합니다. =XLOOKUP("태블릿", B2:B4, A2:A4)
결과: P002
예제 목표 2: 'P001'의 단가를 조회하세요.
해결 방법 2 (제품 코드로 단가 조회): E3 셀에 다음 수식을 입력합니다. =XLOOKUP("P001", A2:A4, C2:C4)
결과: 10,000
최상급 실무 활용: XLOOKUP은 VLOOKUP의 가장 큰 제약(왼쪽 검색 불가)을 단번에 해결합니다. 찾을_범위와 반환할_범위를 별도로 지정함으로써, 데이터 테이블의 구조와 상관없이 원하는 값을 유연하게 찾아올 수 있습니다. 이는 데이터 테이블의 순서를 바꿀 필요 없이 바로 적용할 수 있어 시간을 크게 절약해 줍니다.
실무 예제 2 (기본 오류 처리): 위 제품 정보 테이블에서 존재하지 않는 제품 코드를 조회했을 때, #N/A 대신 사용자 정의 메시지를 표시하세요.
예제 목표: 'P005'의 단가를 조회하되, 없을 경우 "해당 제품 정보 없음"이라고 표시하세요.
해결 방법: E4 셀에 다음 수식을 입력합니다. =XLOOKUP("P005", A2:A4, C2:C4, "해당 제품 정보 없음")
결과: 해당 제품 정보 없음
최상급 실무 활용: IFERROR(VLOOKUP(...), "...")와 같이 IFERROR를 중첩할 필요 없이, XLOOKUP 자체적으로 찾을_값이_없을_경우_값 인수를 제공하여 수식을 훨씬 간결하게 만듭니다. 이는 보고서의 가독성과 유지보수성을 크게 향상시킵니다.
실무 예제 3 (역방향 검색 및 여러 값 반환): 일련의 거래 기록에서 가장 최근에 발생한 특정 제품의 판매액을 조회해야 합니다.
| 거래 ID | 날짜 | 제품명 | 판매액 |
| T001 | 2025-01-05 | 노트북 | 100 |
| T002 | 2025-01-10 | 태블릿 | 120 |
| T003 | 2025-01-15 | 노트북 | 150 |
| T004 | 2025-01-20 | 스마트폰 | 80 |
| T005 | 2025-01-25 | 노트북 | 130 |
예제 목표 1: '노트북'의 가장 최근 판매액을 조회하세요. (가장 마지막에 나타나는 '노트북' 판매액)
해결 방법 1 (가장 최근 판매액 - 역방향 검색): G2 셀에 다음 수식을 입력합니다. =XLOOKUP("노트북", C2:C6, D2:D6, "", 0, -1)
- 0: 정확히 일치
- -1: 끝에서부터 처음까지 검색 (가장 마지막에 나타나는 일치 항목)
결과: 130
예제 목표 2: '노트북'에 대한 가장 최근 거래의 '판매액'과 '날짜'를 동시에 가져오세요.
해결 방법 2 (가장 최근 판매의 판매액과 날짜 동시 반환): H2 셀에 다음 수식을 입력합니다. (결과가 H2와 I2에 걸쳐 나타남) =XLOOKUP("노트북", C2:C6, B2:D6, "", 0, -1)
- 반환할_범위를 B2:D6 (날짜부터 판매액까지)로 지정하면, 일치하는 행의 해당 범위 전체를 반환합니다.
결과: H2에 2025-01-25, I2에 노트북, J2에 130 (동적 배열로 반환)
최상급 실무 활용: XLOOKUP의 검색_모드와 여러 값 반환 기능은 가장 최신/오래된 거래 조회, 특정 상태의 마지막 변경 기록 확인, 여러 관련 필드를 한 번에 가져오기 등 복잡한 데이터 조회 요구사항을 단일 함수로 해결합니다. 이는 재고 관리, 고객 이력 추적, 로그 분석 등 시계열 데이터가 포함된 보고서에서 매우 유용합니다.
2. XMATCH: MATCH 함수의 현대적 대안
XMATCH는 MATCH 함수와 동일하게 값의 상대적인 위치를 반환하지만, XLOOKUP과 유사하게 향상된 기능을 제공합니다.
- 기본 형식: =XMATCH(찾을_값, 찾을_범위, [일치_모드], [검색_모드])
- 인수는 XLOOKUP의 찾을_값, 찾을_범위, 일치_모드, 검색_모드와 동일합니다.
실무 예제: 위 거래 기록에서 '스마트폰'이 '제품명' 열에서 몇 번째에 위치하는지, 그리고 역방향 검색으로 가장 마지막 '노트북'이 몇 번째에 위치하는지 찾으세요.
예제 목표 1: '스마트폰'의 상대적인 위치 찾기.
해결 방법 1: K2 셀에 =XMATCH("스마트폰", C2:C6)
결과: 4
예제 목표 2: 가장 마지막 '노트북'의 상대적인 위치 찾기.
해결 방법 2: K3 셀에 =XMATCH("노트북", C2:C6, 0, -1)
결과: 5
최상급 실무 활용: XMATCH는 MATCH를 대체하며, XLOOKUP과 결합하여 더욱 강력하고 유연한 INDEX/MATCH 대안을 만들 수 있습니다. 특히 일치_모드와 검색_모드를 활용하여 다양한 조건의 위치를 찾아낼 때 유용합니다.
엑셀 조회 함수의 새로운 표준인 XLOOKUP과 그 동반자 XMATCH를 심층적으로 살펴보았습니다. XLOOKUP은 VLOOKUP과 INDEX/MATCH의 장점을 통합하고, 오류 처리, 역방향 검색, 여러 값 반환 등 강력한 추가 기능을 제공하여 데이터 조회 작업을 훨씬 간결하고 효율적으로 만듭니다.