엑셀함수/XLOOKUP/특정 값을 기준으로 데이터를 찾을 때 더 강력하고 유용한 함수
1. XLOOKUP 함수란?
XLOOKUP은 특정 값을 검색 범위에서 찾아서, 해당 값이 있는 위치의 다른 열 또는 행의 값을 반환하는 함수입니다.
기존 VLOOKUP과 HLOOKUP의 기능을 통합하고, INDEX + MATCH 조합보다 쉽게 사용할 수 있습니다.
✅ XLOOKUP을 사용해야 하는 경우
🔹 VLOOKUP처럼 특정 키 값으로 데이터를 찾고 싶을 때
🔹 VLOOKUP과 달리 왼쪽 방향 검색도 필요할 때
🔹 VLOOKUP에서 열이 추가되거나 삭제될 때 발생하는 문제를 피하고 싶을 때
🔹 #N/A 오류를 방지하고 기본값을 설정하고 싶을 때
2. XLOOKUP 함수 문법
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
인수설명
| lookup_value | 찾고자 하는 값 (검색할 키 값) |
| lookup_array | 검색할 범위 (이 범위에서 lookup_value를 찾음) |
| return_array | 찾은 값과 같은 행/열에서 반환할 데이터 범위 |
| [if_not_found] (선택) | 검색 값이 없을 때 반환할 기본값 (예: "값 없음") |
| [match_mode] (선택) | 0: 정확한 일치(기본값), -1: 작은 값 중 가장 가까운 값, 1: 큰 값 중 가장 가까운 값, 2: 와일드카드 검색 |
| [search_mode] (선택) | 1: 처음부터 검색(기본값), -1: 뒤에서부터 검색, 2: 이진 검색(오름차순 정렬 필요), -2: 이진 검색(내림차순 정렬 필요) |
3. XLOOKUP 함수 예제
예제 1: 제품 코드로 제품명 찾기
다음과 같은 제품 목록이 있다고 가정합니다.
제품코드제품명가격
| A001 | 노트북 | 1,500,000 |
| A002 | 키보드 | 50,000 |
| A003 | 마우스 | 30,000 |
| A004 | 모니터 | 300,000 |
이제 A003 제품 코드를 입력하면 해당 제품명을 찾는 XLOOKUP 함수를 작성해 보겠습니다.
=XLOOKUP("A003", A2:A5, B2:B5)
✅ "A003"을 A2:A5 범위에서 찾고, 같은 행에서 B2:B5 범위의 값을 반환
✅ 결과: "마우스"
예제 2: 셀에 입력된 제품 코드로 제품명 찾기
제품 코드를 E2 셀에 입력하면 자동으로 제품명을 가져오도록 설정할 수도 있습니다.
=XLOOKUP(E2, A2:A5, B2:B5)
✅ E2에 A002를 입력하면 결과는 "키보드"
✅ E2에 A004를 입력하면 결과는 "모니터"
예제 3: 가격 정보 가져오기
제품 코드(E2)를 입력하면 해당 제품의 가격을 가져오고 싶다면?
=XLOOKUP(E2, A2:A5, C2:C5)
✅ E2에 A001을 입력하면 결과는 1,500,000
✅ E2에 A004를 입력하면 결과는 300,000
4. XLOOKUP의 강력한 기능
1) 값이 없을 때 기본값 반환 ([if_not_found])
검색 값이 없으면 기본적으로 #N/A 오류가 발생합니다.
이를 방지하려면 [if_not_found] 인수를 추가하여 기본값을 설정할 수 있습니다.
=XLOOKUP(E2, A2:A5, B2:B5, "찾을 수 없음")
✅ E2에 없는 제품 코드를 입력하면 **"찾을 수 없음"**이 표시됨
2) VLOOKUP과 달리 왼쪽 방향 검색 가능
VLOOKUP은 항상 오른쪽 방향으로만 검색할 수 있지만, XLOOKUP은 왼쪽 방향도 검색 가능합니다.
예제: 제품명으로 제품 코드 찾기
=XLOOKUP(E2, B2:B5, A2:A5)
✅ E2에 "마우스"를 입력하면 결과는 "A003"
(제품명이 B2:B5에 있고, 제품 코드는 A2:A5에 있음)
3) 와일드카드 검색 ([match_mode])
검색 값이 특정 단어를 포함하는 경우 검색하려면 match_mode=2를 사용하면 됩니다.
=XLOOKUP("*노트*", B2:B5, A2:A5, "없음", 2)
✅ "노트"가 포함된 제품명의 제품 코드를 찾음 → "A001"
✅ E2에 "모니터"를 입력하면 결과는 "A004"
4) 마지막 값 검색 ([search_mode])
search_mode=-1을 사용하면 리스트에서 마지막으로 나타나는 값을 검색할 수 있습니다.
=XLOOKUP(E2, A2:A10, B2:B10, "없음", 0, -1)
✅ 여러 개의 동일한 값이 있을 때 마지막 값을 반환
5) 여러 개의 열에서 데이터 가져오기
return_array를 여러 개의 열로 지정하면 한 번에 여러 개의 값을 반환할 수도 있습니다.
=XLOOKUP(E2, A2:A5, B2:C5)
✅ E2에 A002를 입력하면 결과는 "키보드", 50,000 (두 개의 값을 반환)
5. VLOOKUP vs XLOOKUP 비교
기능VLOOKUPXLOOKUP
| 검색 방향 | 오른쪽만 가능 | 왼쪽 & 오른쪽 모두 가능 |
| 열 번호 사용 | col_index_num 사용 | 필요 없음 (자동 선택) |
| 기본값 설정 | IFERROR(VLOOKUP(...)) 사용해야 함 | [if_not_found] 인수로 쉽게 설정 |
| 여러 개의 열 반환 | 불가능 | 가능 (return_array에 여러 열 지정) |
| 와일드카드 검색 | 불가능 | 가능 (match_mode=2) |
| 마지막 값 검색 | 불가능 | 가능 (search_mode=-1) |
✅ XLOOKUP은 VLOOKUP보다 더 강력하고 유연하게 데이터 검색이 가능합니다.
✅ VLOOKUP의 단점(열 추가 시 오류, 오른쪽 방향만 검색 가능 등)을 해결할 수 있습니다.
6. 결론
- XLOOKUP은 VLOOKUP과 HLOOKUP의 기능을 개선한 최신 검색 함수입니다.
- 왼쪽 방향 검색, 기본값 설정, 여러 개의 열 반환, 마지막 값 검색 등의 기능을 지원합니다.
- VLOOKUP보다 사용이 간편하고 강력하므로 엑셀 2019 이상 버전에서는 XLOOKUP을 적극 활용하는 것이 좋습니다. 🚀