매일 아침 출근하자마자 수만 행짜리 로우 데이터(Raw Data)와 씨름하는 직장인에게 데이터 조회 함수는 업무 효율을 결정하는 핵심 도구입니다. 그중에서도 VLOOKUP은 가장 대중적인 함수로 자리 잡았습니다. 하지만 보고서 마감 직전 원본 데이터에 열(Column) 하나를 추가했다가 수식이 깨져 #REF! 오류를 마주하거나, 찾으려는 기준 열이 조회할 데이터의 오른쪽에 위치해 INDEX와 MATCH를 복잡하게 조합하느라 고생한 경험이 한 번쯤은 있을 것입니다.
마이크로소프트는 이러한 VLOOKUP의 고질적인 한계를 해결하기 위해 최신 엑셀 버전(Office 365 및 2021 이상)에 XLOOKUP 함수를 도입했습니다. XLOOKUP은 단 하나의 함수로 기존 VLOOKUP, HLOOKUP, IFERROR, INDEX/MATCH 조합의 기능까지 모두 대체하는 강력함을 자랑합니다. 본 글에서는 실무에서 바로 적용할 수 있는 XLOOKUP의 핵심 활용법을 단계별로 자세히 설명합니다.
🔑 핵심 요약 (Key Takeaways)
- XLOOKUP은 데이터의 방향(왼쪽, 오른쪽, 위, 아래)에 구애받지 않고 유연하게 값을 조회합니다.
- 원본 테이블에 열이 추가되거나 삭제되어도 참조 범위가 깨지지 않아 수식의 안정성이 극대화됩니다.
- 별도의 IFERROR 함수 없이도 자체 인수만으로 깔끔하게 오류(#N/A) 처리가 가능합니다.
목차
1. VLOOKUP의 치명적인 한계와 XLOOKUP의 등장
VLOOKUP은 엑셀 사용자들에게 오랜 기간 사랑받아 왔지만, 실무 환경에서 다음과 같은 치명적인 한계를 드러냅니다.
- 오른쪽 방향으로만 검색 가능: 기준 열을 기준으로 오른쪽에 있는 데이터만 가져올 수 있어, 왼쪽에 있는 데이터를 찾으려면 INDEX/MATCH 함수를 복잡하게 중첩해야 합니다.
- 열 삽입/삭제 시 수식 손상: 세 번째 인수로 열 번호(Index Number)를 직접 숫자로 입력하기 때문에, 원본 데이터 중간에 열이 추가되면 엉뚱한 값을 불러오거나 오류가 납니다.
- 기본값이 유사 일치(TRUE): 마지막 인수를 생략하면 정확히 일치하는 값이 아닌 유사한 값을 찾아 잘못된 보고서가 작성될 위험이 큽니다.
이러한 문제들을 한 번에 해결하기 위해 출시된 함수가 바로 XLOOKUP입니다. 두 함수의 핵심 차이점을 표로 비교해 보겠습니다.
| 비교 항목 | VLOOKUP 함수 | XLOOKUP 함수 |
|---|---|---|
| 검색 방향 | 오른쪽 방향만 가능 | 모든 방향 가능 (왼쪽, 위, 아래) |
| 열 추가/삭제 영향 | 수식이 깨지거나 잘못된 값 반환 | 안전함 (참조 범위 자동 유지) |
| 기본 일치 옵션 | 유사 일치 (TRUE) | 정확히 일치 (0) |
| 오류 처리 (#N/A) | IFERROR 함수 추가 중첩 필요 | 자체 인수로 해결 (4번째 인수) |
2. XLOOKUP 함수의 기본 문법과 인수 구성
XLOOKUP 함수는 최대 6개의 인수를 가질 수 있지만, 실무에서는 앞의 3개 필수 인수만 알아도 대부분의 조회가 가능합니다.
- lookup_value (필수): 찾으려는 값입니다. (예: 사원번호, 제품코드)
- lookup_array (필수): 찾을 값이 포함된 원본 데이터의 범위(1차원 열 또는 행)입니다.
- return_array (필수): 가져오고 싶은 결과 데이터가 포함된 범위입니다.
- if_not_found (선택): 일치하는 값을 찾지 못했을 때 반환할 텍스트나 값입니다. (예: "데이터 없음")
- match_mode (선택): 일치 방식을 지정합니다. (0: 정확히 일치 - 기본값, -1: 작은 값 일치, 1: 큰 값 일치, 2: 와일드카드 일치)
- search_mode (선택): 검색 방향을 지정합니다. (1: 첫 번째 항목부터 - 기본값, -1: 마지막 항목부터 역방향 검색)
3. [실무 예제 1] 왼쪽 방향 데이터 조회 (엑셀 왼쪽 찾기)
인사 관리나 재고 관리 데이터를 다룰 때, 기준 열의 왼쪽에 위치한 데이터를 조회해야 하는 경우가 빈번합니다. VLOOKUP으로는 불가능했던 이 작업을 XLOOKUP은 아주 직관적으로 해결합니다.
예를 들어, B열에 '사원번호'가 있고 A열에 '이름'이 있다고 가정하겠습니다. 사원번호 "E105"를 기준으로 이름을 조회하는 상황입니다.
이 수식은 B열(B2:B10)에서 "E105"를 찾은 뒤, 동일한 행의 A열(A2:A10) 데이터를 즉시 반환합니다. 범위 전체를 지정하는 대신 찾을 열과 가져올 열을 각각 1차원 범위로 지정하기 때문에, 왼쪽에 위치한 데이터도 제약 없이 조회할 수 있습니다.
4. [실무 예제 2] 다중 조건으로 데이터 검색 (엑셀 다중 조건 조회)
실무에서는 단 하나의 기준이 아닌, 여러 조건을 동시에 만족하는 데이터를 찾아야 하는 경우가 많습니다. 예를 들어, '연도'와 '부서명'이 동시에 일치하는 '매출액' 데이터를 조회해야 할 때입니다.
기존에는 도우미 열을 임시로 만들어 두 텍스트를 병합하거나 복잡한 배열 수식을 작성해야 했지만, XLOOKUP에서는 논리곱(*) 연산자를 활용해 명료하게 작성할 수 있습니다.
이 수식의 원리는 간단합니다. 각 조건식은 조건이 맞으면 TRUE(1), 틀리면 FALSE(0)를 반환합니다. 두 범위를 곱하면 두 조건이 모두 참인 경우에만 1 * 1 = 1이 됩니다. 따라서 첫 번째 인수로 1을 지정하고, 조건들의 곱 범위에서 1이 있는 위치를 찾아 C열의 매출액을 가져오는 구조입니다. 직관적이면서도 강력한 다중 조건 검색 방법입니다.
5. [실무 예제 3] #N/A 오류 대체 및 정확한 일치 옵션
조회하려는 데이터가 원본에 없을 때 발생하는 #N/A 오류는 가독성을 떨어뜨리고 후속 연산에 지장을 줍니다. XLOOKUP은 네 번째 인수를 사용해 이 문제를 깔끔하게 예방합니다.
위와 같이 작성하면, F2 셀의 사원번호가 원본 범위에 없을 경우 에러 메시지 대신 "등록되지 않은 사원"이라는 문구를 깨끗하게 출력합니다. 중첩 함수를 쓰지 않아 수식이 한결 단순해집니다.
또한, 마지막 인수를 활용해 최신 등록 데이터(마지막 행에 위치한 데이터)를 먼저 검색하고 싶다면 여섯 번째 인수에 -1을 적용하면 됩니다. 역방향 검색이 필요할 때 매우 유용합니다.
6. XLOOKUP 사용 시 자주 발생하는 실수와 해결 방법
XLOOKUP이 매우 편리한 함수임은 분명하지만, 실무에서 실수하기 쉬운 몇 가지 주의점이 있습니다.
- 참조 범위의 크기 불일치:
lookup_array와return_array의 행 크기가 서로 다르면#VALUE!오류가 발생합니다. 예를 들어 하나는A2:A10인데 다른 하나가B2:B11로 지정되지 않았는지 반드시 확인해야 합니다. 범위의 시작과 끝 행 번호는 항상 일치해야 합니다. - 하위 버전과의 호환성 문제: XLOOKUP은 Microsoft 365, Excel 2021 및 웹용 엑셀에서만 작동합니다. Excel 2019 이하 버전을 사용하는 외부 협력사나 사내 다른 팀원과 파일을 공유할 경우, 수식이 정상적으로 동작하지 않고
#NAME?오류가 뜰 수 있습니다. 이 경우 부득이하게 INDEX/MATCH 조합을 사용해야 합니다.
7. 자주 묻는 질문 (FAQ)
Q1. XLOOKUP 함수를 실행했는데 #NAME? 오류가 납니다. 어떻게 해야 하나요?
A1. 이 오류는 현재 사용 중인 엑셀 버전이 XLOOKUP을 지원하지 않는 하위 버전(예: Excel 2019 이하)일 때 발생합니다. Microsoft 365 버전으로 업데이트하거나, INDEX와 MATCH 함수 조합으로 수식을 변경해야 합니다.
Q2. VLOOKUP의 TRUE(유사 일치) 옵션처럼 구간별 등급을 매기는 작업도 XLOOKUP으로 가능한가요?
A2. 가능합니다. 다섯 번째 인수(match_mode)를 -1(정확히 일치하거나 다음으로 작은 항목) 또는 1(정확히 일치하거나 다음으로 큰 항목)로 설정하면 구간별 기준표를 참조해 원활하게 등급을 매길 수 있습니다.
Q3. 여러 개의 열 데이터를 한 번에 가져올 수도 있나요?
A3. 네, XLOOKUP의 강력한 특징 중 하나입니다. 세 번째 인수(return_array)에 단일 열이 아닌 여러 열 범위(예: B2:D10)를 지정하면, 수식을 옆으로 드래그할 필요 없이 한 번에 여러 열의 값이 우측으로 자동 분산(Spill)되어 출력됩니다.
🎯 오늘의 실무 실천 체크리스트
- 사내 PC의 엑셀 버전이 Microsoft 365 혹은 Excel 2021 이상인지 확인하기
- 기존에 작성된 중요 수식 중 열 삽입 시 깨질 우려가 있는 VLOOKUP 수식을 XLOOKUP으로 교체하기
- IFERROR 함수를 걷어내고 XLOOKUP의 네 번째 인수를 활용해 수식을 단순화해 보기
- 다중 조건 조회가 필요한 업무에서 임시 열을 만드는 대신 논리곱(*) 수식 적용하기