Excel은 데이터 관리와 분석을 위한 다양한 조회(Lookup) 기법을 제공합니다. VLOOKUP은 데이터 검색에 널리 사용되지만, 조회 열이 항상 왼쪽에 있어야 하고 오류 처리가 유연하지 못하다는 등의 한계가 있습니다. 이러한 한계는 XLOOKUP과 INDEX-MATCH-MATCH 같은 고급 함수로 극복할 수 있으며, 이를 활용하면 훨씬 더 큰 유연성과 제어력, 효율성을 얻을 수 있습니다.
이 글에서는 실제 판매 데이터셋을 예시로 삼아, VLOOKUP 이상의 성능을 발휘하는 고급 조회 기법을 하나씩 살펴보겠습니다.
XLOOKUP 고급 조회 기법
XLOOKUP은 단일 열 또는 여러 열 조회에 모두 활용할 수 있는 Excel의 강력한 함수입니다. 검색 방향의 제약이 없어(왼쪽에서 오른쪽, 오른쪽에서 왼쪽, 세로, 가로 모두 가능) 원하는 위치에서 데이터를 찾을 수 있고, 사용자 지정 오류 메시지를 제공하며, 정렬되지 않은 데이터도 문제없이 조회합니다. 또한 조회 값이 변경되면 결과가 자동으로 업데이트됩니다. 단, XLOOKUP은 Excel 2021 및 Microsoft 365 사용자만 사용할 수 있다는 점에 유의하세요.
XLOOKUP 구문
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: 찾으려는 값입니다.
- lookup_array: 검색할 범위 또는 배열입니다.
- return_array: 결과를 반환할 범위 또는 배열입니다.
- [if_not_found] (선택): 일치하는 값이 없을 때 반환할 값입니다.
- [match_mode] (선택): 일치 유형을 지정합니다. 정확히 일치, 와일드카드, 근사값 일치 중 선택할 수 있습니다.
- 0 – 정확히 일치(기본값)
- 1 – 정확히 일치 또는 다음으로 큰 값
- -1 – 정확히 일치 또는 다음으로 작은 값
- 2 – 와일드카드 일치
- [search_mode] (선택): 검색 방향을 지정합니다.
- 1 – 처음부터 끝까지 검색
- -1 – 끝에서 처음까지 검색
- 2 – 이진 검색(오름차순)
- -2 – 이진 검색(내림차순)
1. XLOOKUP으로 단일 조건 조회하기
판매 데이터셋에서 $100에 가장 가까운 판매 금액을 찾아, 한 번의 주문에 약 $100를 지출한 고객을 확인해 보겠습니다. 아래 수식을 입력하세요.
수식:
=XLOOKUP(100, G2:G71, A2:G71, "Not Found", 1)
이 수식은 G2:G71 범위에서 100을 검색하고, A2:G71에서 해당 행 전체를 반환합니다. match_mode를 1로 설정했기 때문에 정확한 값이 없으면 다음으로 큰 근사값을 찾으며, 그래도 찾지 못하면 "Not Found"가 표시됩니다.
실행 결과:
1007 | 2024-01-04 | Daniel Martinez | East | 39.99 | 3 | 119.97
2. XLOOKUP으로 다중 조건 조회하기
XLOOKUP에서는 여러 조건을 연결(&)하여 더 복잡한 조회를 수행할 수 있습니다. 예를 들어 특정 고객명과 지역을 동시에 만족하는 레코드를 찾아보겠습니다.
수식:
=XLOOKUP("Melissa Lopez" & "West", C2:C71 & D2:D71, A2:G71)
이 수식은 고객 이름과 지역을 각각 연결한 뒤, 연결된 조회 배열에서 결합된 값을 찾아 선택한 범위에서 해당 행을 반환합니다.
실행 결과:
1012 | 2024-01-06 | Melissa Lopez | West | 79.99 | 2 | 159.98
INDEX-MATCH-MATCH 고급 조회 기법
INDEX-MATCH-MATCH는 행과 열 두 가지 기준을 모두 만족하는 값을 조회해야 할 때 사용하며, 2차원 데이터 표에 특히 적합합니다.
INDEX-MATCH-MATCH 구문
=INDEX(array, MATCH(row_lookup_value, row_lookup_array, 0), MATCH(column_lookup_value, column_lookup_array, 0))
- array: 가져오려는 값이 포함된 셀 범위입니다.
- MATCH(row_lookup_value, row_lookup_array, 0): 행 번호를 반환합니다.
- row_lookup_value: 행에서 찾으려는 값입니다.
- row_lookup_array: 검색할 행 범위입니다.
- 0 – 정확히 일치
- MATCH(column_lookup_value, column_lookup_array, 0): 열 번호를 반환합니다.
- column_lookup_value: 열에서 찾으려는 값입니다.
- column_lookup_array: 검색할 열 범위입니다.
- 0 – 정확히 일치
1. 행과 열을 결합한 2차원 조회
INDEX-MATCH-MATCH 수식을 사용해 특정 고객의 판매 금액(Sales Amount)을 확인해 보겠습니다. 아래 수식을 입력하세요.
수식:
=INDEX(A2:G71, MATCH("John Smith", C2:C71, 0), MATCH("Sales Amount", A1:G1, 0))
이 수식은 C2:C71 범위에서 "John Smith"를, A1:G1 범위에서 "Sales Amount"를 검색한 후, 두 위치가 교차하는 A2:G71의 값을 반환합니다.
실행 결과:
99.98
2. 3차원 조회를 위한 고급 INDEX-MATCH-MATCH 수식
INDEX-MATCH-MATCH는 기본적으로 2차원 조회에 사용되지만, 여러 MATCH 조건을 곱셈으로 결합한 배열 수식을 활용하면 3차원 조회로 확장할 수 있습니다. 대용량 데이터셋과 복잡한 조건 매칭에 적합하며, 유연성과 성능 면에서 VLOOKUP보다 뛰어납니다.
수식:
=INDEX(G2:G71, MATCH(1, (C2:C71="John Smith") * (D2:D71="South") * (F2:F71=4), 0))
이 수식은 세 가지 조건, 즉 C열의 고객명("John Smith"), D열의 지역("South"), F열의 수량(4)을 모두 만족하는 Sales Amount를 검색합니다. 각 조건은 곱셈(AND 논리)으로 결합되어, MATCH가 세 조건을 모두 충족하는 행을 찾으면 INDEX가 G2:G71에서 해당 값을 반환합니다.
실행 결과:
129.99
XLOOKUP과 INDEX-MATCH 사용의 장점
VLOOKUP 대신 XLOOKUP을 선택해야 하는 이유
- 양방향 검색: XLOOKUP은 어느 방향으로든 조회할 수 있지만, VLOOKUP은 왼쪽에서 오른쪽으로만 검색할 수 있습니다.
- 열 번호 불필요: 열 인덱스 번호를 지정할 필요가 없어 열이 추가되거나 삭제되어도 수식이 깨지지 않습니다.
- 기본 정확히 일치: XLOOKUP은 기본적으로 정확히 일치를 검색하므로 오류 발생 가능성이 줄어듭니다.
- 와일드카드 검색 지원: * 및 ? 같은 와일드카드를 활용해 텍스트를 유연하게 검색할 수 있습니다.
- 손쉬운 오류 처리: 일치하는 값이 없을 때 반환할 값을 수식 안에서 직접 지정할 수 있습니다.
VLOOKUP 대비 INDEX-MATCH의 장점
- 유연한 표 구조: 열을 삽입하거나 삭제해도 INDEX-MATCH 수식은 영향을 받지 않습니다.
- 왼쪽 조회 가능: VLOOKUP과 달리 조회 열 기준 왼쪽에 있는 값도 가져올 수 있습니다.
- 우수한 성능: 상황에 따라 MATCH가 VLOOKUP 재계산보다 빠르므로 대용량 데이터셋에서 더 효율적입니다.
마무리
XLOOKUP과 INDEX-MATCH-MATCH 같은 고급 조회 기법은 Excel에서 데이터를 전문적으로 다루기 위한 필수 도구입니다. 두 함수 모두 데이터 변경 사항을 자동으로 반영하며, 기존 VLOOKUP을 능가하는 유연성·정확성·성능을 제공합니다. Excel 실력을 한 단계 끌어올리고 싶다면 오늘 소개한 기법들을 실무에 바로 적용해 보세요.
무료 고급 Excel 연습 문제와 해답을 받아보세요!