엑셀에서 데이터를 찾아야 할 때 VLOOKUP 때문에 고생해보신 적 있으신가요? 특히 왼쪽에 있는 데이터를 찾을 때 말이죠. 오늘은 이런 불편함을 한 번에 해결해주는 XLOOKUP 함수에 대해 알아보겠습니다!
등장 배경
과거에는 엑셀에서 데이터를 찾을 때 VLOOKUP과 HLOOKUP 함수를 주로 사용했습니다. 1985년 Excel 1.0 for Macintosh부터 존재했던 VLOOKUP은 무려 35년간 엑셀의 대표적인 검색 함수로 자리잡았었죠.
SUM과 AVERAGE 다음으로 세 번째로 많이 사용되는 함수였습니다!
하지만 VLOOKUP에는 여러 제약사항이 있었습니다:
- 왼쪽 데이터 검색 불가 🚫: VLOOKUP은 항상 첫 번째 열에서 검색해서 오른쪽 열의 값만 반환 가능
- 기본값이 근사일치: 정확한 일치를 원할 때 FALSE를 명시해야 함
- 열 삽입/삭제 시 문제: 열 번호를 직접 수정해야 함
- 마지막 값 검색 불가: 첫 번째 일치 값만 반환
이런 불편함을 해결하기 위해 Microsoft는 2019년에 XLOOKUP 함수를 발표했고, 2020년부터 Office 365 사용자들에게 제공되기 시작했습니다.
핵심 원리
XLOOKUP의 작동 방식을 시각적으로 표현하면 다음과 같습니다:
┌─────────────────────────────────────────────┐
│ XLOOKUP 함수 구조 │
├─────────────────────────────────────────────┤
│ │
│ =XLOOKUP(찾을값, 검색범위, 반환범위, ...) │
│ │
│ ↓ ↓ ↓ │
│ [검색값] [어디서?] [무엇을?] │
│ │
└─────────────────────────────────────────────┘
XLOOKUP 함수의 매개변수:
| 매개변수 | 설명 | 필수/선택 |
|---|---|---|
| lookup_value | 찾고자 하는 값 | 필수 |
| lookup_array | 검색할 범위 | 필수 |
| return_array | 반환할 값이 있는 범위 | 필수 |
| [if_not_found] | 값을 찾지 못했을 때 표시할 내용 | 선택 |
| [match_mode] | 일치 방식 (0: 정확히, -1: 작거나 같은, 1: 크거나 같은, 2: 와일드카드) | 선택 |
| [search_mode] | 검색 방향 (1: 처음부터, -1: 끝부터, 2/-2: 이진 검색) | 선택 |
VLOOKUP vs XLOOKUP 비교:
VLOOKUP의 한계:
┌─────┬─────┬─────┬─────┐
│ A │ B │ C │ D │
├─────┼─────┼─────┼─────┤
│ │ ← 검색 불가능 │
│ ↓ │ ↓ │ → │ → │
│ 검색│ │반환 │반환 │
└─────┴─────┴─────┴─────┘
XLOOKUP의 자유로움:
┌─────┬─────┬─────┬─────┐
│ A │ B │ C │ D │
├─────┼─────┼─────┼─────┤
│ ← │ ← │ ← │ ← │
│ → │ → │ → │ → │
│ 어디서든 검색/반환 가능! │
└─────┴─────┴─────┴─────┘
실제 사용 예시:
직원 데이터:
┌────────┬─────────┬────────────┬─────────┐
│ 사번 │ 이름 │ 부서 │ 연봉 │
├────────┼─────────┼────────────┼─────────┤
│ E001 │ 김철수 │ 영업부 │ 5,000 │
│ E002 │ 이영희 │ 개발부 │ 6,000 │
│ E003 │ 박민수 │ 인사부 │ 4,500 │
└────────┴─────────┴────────────┴─────────┘
=XLOOKUP("이영희", B2:B4, D2:D4)
결과: 6,000
=XLOOKUP("E003", A2:A4, B2:D4)
결과: {"박민수", "인사부", 4,500} (전체 행 반환!)주의사항 및 팁 💡
⚠️ 이것만은 주의하세요!
- 버전 호환성 문제
- XLOOKUP은 Office 365, Excel 2021 이상에서만 사용 가능
- 이전 버전에서 파일을 열면 #NAME? 오류 발생
- 해결 방법: 공유 시 버전 확인 또는 VLOOKUP으로 대체
- 대용량 데이터 처리
- 이진 검색(search_mode 2/-2) 사용 시 데이터가 정렬되어 있어야 함
- 정렬되지 않은 데이터에 이진 검색 사용 시 잘못된 결과 반환
- 해결 방법: 데이터 정렬 확인 또는 기본 검색 모드 사용
- 배열 크기 불일치
- lookup_array와 return_array의 크기가 다르면 #VALUE! 오류
- 해결 방법: 두 배열의 행/열 수가 일치하는지 확인
💡 꿀팁
- 다중 값 반환: return_array를 여러 열로 지정하면 한 번에 여러 값 반환 가능
- 오류 처리: if_not_found 매개변수로 "데이터 없음" 같은 친근한 메시지 표시
- 역방향 검색: search_mode를 -1로 설정하면 마지막 일치 항목 찾기 가능
- 정규표현식 지원: 2024년 12월부터 match_mode 3으로 정규표현식 사용 가능 (Office 365만)
마치며
지금까지 XLOOKUP 함수에 대해 알아보았습니다. 처음에는 VLOOKUP에 익숙해서 어색할 수 있지만, 한 번 사용해보면 그 편리함에 놀라실 거예요! 특히 왼쪽 데이터를 찾거나 여러 값을 한 번에 반환할 때 정말 유용합니다.
여러분도 XLOOKUP을 사용해서 더 효율적인 엑셀 작업을 경험해보시길 바랍니다! 혹시 XLOOKUP을 사용하면서 겪은 특별한 경험이나 활용 팁이 있으신가요? 😊
참고 자료 🔖
- XLOOKUP function - Microsoft Support
- Excel XLOOKUP function | Exceljet
- How to use XLOOKUP function with 10 practical examples
#XLOOKUP #엑셀함수 #Excel365 #데이터검색