엑셀을 오래 사용해 왔다면 VLOOKUP이나 INDEX/MATCH를 이미 수없이 활용해 왔을 것입니다. 하지만 이제 VLOOKUP의 한계와 씨름하고, INDEX/MATCH의 복잡한 중첩 구조에 시달리던 시절은 끝났습니다. XLOOKUP은 유연하고 간결하면서도 두 함수를 모두 대체할 만큼 강력한 엑셀의 최신 조회 함수입니다.
이 튜토리얼에서는 XLOOKUP이 여러분의 새로운 필수 도구가 되는 세 가지 이유를 실전 예제와 함께 소개합니다. 복잡한 우회 방법 없이도 일상적인 조회 작업을 더 빠르고 깔끔하게 처리하는 방법을 지금 확인해 보세요.
기본 구문
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
XLOOKUP 함수는 한 열(또는 행)에서 특정 값을 찾아 다른 열(또는 행)에 있는 관련 값을 반환하는 엑셀의 최신 조회 함수입니다. 더 간단하고 유연하게 사용할 수 있도록 설계되어 VLOOKUP과 INDEX/MATCH를 완벽하게 대체하는 것을 목표로 만들어졌습니다.
이유 1: 좌우 방향 제약 없이 자유로운 조회 가능
VLOOKUP은 조회 열의 오른쪽만 검색할 수 있습니다. 조회 기준 열이 테이블 범위의 첫 번째 열이 아니면 수식은 실패하고 #N/A 오류를 반환합니다. INDEX/MATCH는 이 문제를 해결하지만 두 함수를 중첩해야 하기 때문에 가독성이 떨어지고, 데이터가 커질수록 수식 관리가 번거로워집니다.
XLOOKUP은 이 구조를 완전히 뒤집습니다. 조회 범위와 반환 범위가 서로 분리되어 있어 왼쪽, 오른쪽, 위, 아래 어느 방향으로든 제약 없이 조회할 수 있습니다.
예를 들어 판매 데이터에서 '제품명'으로 검색하여 해당 '제품 ID'를 찾고 싶다고 가정해 보겠습니다.
VLOOKUP:
- 원하는 셀을 선택하고 아래 수식을 입력합니다.
=VLOOKUP(H5,A2:F23,1,FALSE)
이 수식은 #N/A 오류를 반환합니다. VLOOKUP은 왼쪽 방향으로 조회할 수 없기 때문입니다(시트에서 제품 ID가 제품명보다 앞쪽 열에 위치). VLOOKUP을 사용하려면 열 순서를 재배치해야 하는데, 이는 데이터 구조와 기존 수식들을 망가뜨릴 수 있어 현실적으로 어려운 경우가 많습니다.
INDEX/MATCH:
- 원하는 셀을 선택하고 INDEX/MATCH 조합 수식을 입력합니다.
=INDEX(A2:A23, MATCH(H8, B2:B23, 0))
이 수식은 제품 ID(P006)를 올바르게 반환합니다. 하지만 두 개의 범위를 각각 지정해야 하고, MATCH의 일치 옵션(0)까지 신경 써야 해서 수식이 길어지고 복잡해집니다.
XLOOKUP:
XLOOKUP은 열 순서에 전혀 영향을 받지 않습니다. 무엇을 찾을지, 어디서 값을 가져올지만 지정하면 됩니다.
- 원하는 셀을 선택하고 아래 XLOOKUP 수식을 입력합니다.
=XLOOKUP(H12, B2:B23, A2:A23)
이 수식은 제품명을 검색하여 해당 제품 ID(P006)를 반환합니다. ID가 왼쪽 열에 있어도 전혀 문제되지 않으며, 중첩 없이 깔끔한 구문으로 해결됩니다.
실무 활용 팁:
판매 대시보드에서 사용자가 검색창에 제품명을 입력한다고 상상해 보세요. 열을 재배치할 필요 없이 제품 ID는 물론 가격, 재고, 공급업체 같은 여러 정보를 한 번에 불러올 수 있습니다.
=XLOOKUP(H12, B2:B23, A2:E23)
반환 범위를 여러 열로 지정하면 인접한 셀들로 값이 자동 확장(스필)되어, 단 한 번의 수식 입력으로 제품의 모든 정보를 가져올 수 있습니다.
이유 2: 내장된 오류 처리 기능 (#N/A 고민 끝)
조회 값이 존재하지 않으면 엑셀은 #N/A 오류를 반환합니다. VLOOKUP이나 INDEX/MATCH를 사용할 때는 이를 우아하게 처리하려면 IFERROR로 수식을 감싸야 했고, 그만큼 중첩이 깊어지고 수식이 복잡해졌습니다.
XLOOKUP에는 if_not_found 인수가 기본 내장되어 있어, 수식 안에서 바로 친숙한 메시지나 대체 값을 지정할 수 있습니다.
데이터베이스에 존재하지 않을 수 있는 제품 단가(예: "Wireless Charger")를 검색해 보겠습니다.
VLOOKUP + IFERROR:
- 원하는 셀을 선택하고 아래 수식을 입력합니다.
=IFERROR(VLOOKUP(H4,D2:F23,1,FALSE), "Not Found")
동작은 하지만 수식이 장황해지고 중첩 단계가 하나 더 늘어나는 단점이 있습니다.
INDEX/MATCH + IFERROR:
- 원하는 셀을 선택하고 아래 수식을 입력합니다.
=IFERROR(INDEX(D2:D23, MATCH(H8, B2:B23, 0)), "Not Found")
역시 잘 작동하지만, IFERROR로 감싸면서 수식이 더욱 길어집니다.
XLOOKUP:
XLOOKUP의 if_not_found 인수는 누락된 값을 손쉽고 우아하게 처리합니다.
- 원하는 셀을 선택하고 아래 수식을 입력합니다.
=XLOOKUP(H12, B2:B23, D2:D23, "Not Found")
이 수식은 제품명을 검색하고, 찾지 못하면 "Not Found"라는 메시지를 반환합니다. 추가 함수 없이 간결하게 해결됩니다.
이유 3: 와일드카드 검색이 한층 간편해짐
VLOOKUP은 텍스트 조회 시 정확히 일치 모드(FALSE)에서 와일드카드(*, ?)를 지원하지만, 그 의미가 직관적이지 않을 때가 많습니다. INDEX/MATCH 역시 match_type이 0일 때 MATCH가 와일드카드를 인식하므로 사용할 수 있습니다. 반면 XLOOKUP은 match_mode를 2로 설정해 와일드카드 매칭 의도를 명확하게 드러내므로, 가독성이 높아지고 예상치 못한 결과를 줄여 줍니다.
제품명의 일부만 기억나는 상황을 가정해 보겠습니다. 예를 들어 "Lap"으로 시작하는 제품의 가격을 찾고 싶다면 어떻게 할까요?
VLOOKUP:
- 원하는 셀을 선택하고 아래 수식을 입력합니다.
=VLOOKUP("Lap*", B2:D23, 3, FALSE)
정렬되지 않은 데이터에서도 와일드카드를 활용한 검색이 가능합니다. 다만 '열 번호' 인수는 나중에 열이 이동하면 수식이 깨질 수 있는 취약점이 있습니다.
INDEX/MATCH:
- 원하는 셀을 선택하고 아래 수식을 입력합니다.
=INDEX(D2:D23, MATCH("Lap*", B2:B23, 0))
MATCH 덕분에 와일드카드를 지원하며 안정적이지만, 두 개의 범위를 지정하는 구조 탓에 수식이 길어집니다.
XLOOKUP:
XLOOKUP의 match_mode를 활용하면 와일드카드 검색이 훨씬 직관적입니다.
- 원하는 셀을 선택하고 아래 수식을 입력합니다.
=XLOOKUP("Lap*", B2:B23, D2:D23, "No match", 2)
- "Lap*": 부분 일치 ("Lap"으로 시작하는 모든 텍스트와 일치)
- 조회 범위:
B2:B23 - 반환 범위:
D2:D23 - if_not_found: "No match"
- 2: 와일드카드 검색 활성화
이 수식은 와일드카드 매칭으로 제품 가격(예: $30)을 반환합니다. 이름의 일부만 알고 있을 때, SKU 조회, 또는 정확한 값을 모르는 상황에서 특히 유용합니다.
중요 참고 사항: XLOOKUP은 Microsoft 365 및 Excel 2021 이상 버전에서만 사용할 수 있습니다. 구버전을 사용 중이라면 당분간 VLOOKUP이나 INDEX/MATCH를 계속 사용해야 합니다.
와일드카드 활용 팁 정리:
| 기호 | 의미 | 예시 | 일치 결과 |
|---|---|---|---|
| * | 임의 개수의 문자 | "Lap*" | Laptop Stand |
| ? | 단일 문자 하나 | "P00?" | P001, P002 등 |
| ~ | 와일드카드 이스케이프 | "Power~*" | "Power*" 문자 그대로 검색 |
마무리 생각
XLOOKUP은 단순히 또 하나 추가된 조회 함수가 아닙니다. 엑셀에서 조회가 작동하는 방식 자체를 새롭게 재설계한 결과물입니다. 이번 튜토리얼에서 살펴본 것처럼, 방향의 자유로움, 내장 오류 처리, 명시적인 와일드카드 매칭이라는 세 가지 장점을 결합하여 시간을 절약하고 수식 오류를 예방해 줍니다. 한 번 사용해 보면 다시 돌아갈 수 없을 것입니다.