📘엑셀 고급 강의 3‑1. XLOOKUP 함수란? VLOOKUP과 차이점

엑셀에서 주로 사용되던 조회 함수인 VLOOKUP은 사용법이 쉬워 널리 쓰이지만, 실제로는 상당한 제약과 오류 위험이 존재합니다. 예를 들어, 참조 열이 왼쪽에 있어야 한다는 제한, 열 삽입으로 인한 참조 깨짐, 정렬된 데이터만 허용되는 근사 매칭, 오류 처리 불편 등이죠. 이러한 한계를 극복하고자 Microsoft는 Microsoft 365 및 Excel 2021 이후, 더 유연하고 강력한 함수인 XLOOKUP을 도입했습니다.

1. 왜 XLOOKUP이 필요한가요?

• ➡️ 좌우 제한 없음: lookup_array와 return_array가 행의 왼쪽·오른쪽 위치에 상관없이 지정 가능
• ⚠️ 오류 방어 기능: if_not_found 매개변수를 통해 오류 대신 친절한 메시지 출력 가능
• 🔍 정확 일치 뿐 아니라 근사 일치도 지원: match_mode 옵션으로 유연한 조회
• 🔄 역방향 검색: search_mode=-1 설정으로 뒤에서부터 항목 탐색 가능
• ⚙️ 테이블 기반 참조 지원: 열 추가/삭제에도 참조 범위 유지 안정성 확보

---

2. 함수 구조와 인수 설명

=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 = 와일드카드 문자 사용 가능 (예: “ABC*”)
• [search_mode]: 1 = 앞에서부터 검색 (기본), –1 = 뒤에서부터 검색(역방향), 2 = 이진 검색 정렬된 데이터 조건 단축, –2 = 역순 이진 검색

---

3. VLOOKUP vs XLOOKUP 비교 테이블

기능	VLOOKUP	XLOOKUP
열 방향	왼쪽 기준 오른쪽만 참조	양방향 모두 참조 가능
기본 오류 처리	#N/A 오류 표시	if_not_found로 대체 가능
근사 매칭	정렬된 데이터에서만 지원	정렬 없어도 가능
검색 방향	항상 앞에서부터	뒤에서부터도 검색 가능
동적 참조	사용자 실수에 취약	테이블 구조로 안정적 범위 유지

---

4. 실무 시나리오 & 활용 비교

1. 고객 코드 → 고객 정보 조회: 기존 VLOOKUP은 고객 코드 기준 오른쪽에 참조 열이 있어야 했지만, XLOOKUP은 그 반대 방향도 가능해 유연하게 구성할 수 있습니다.
2. 오류 대응 메시지 설정: XLOOKUP 내 if_not_found="고객 없음" 설정하면 오류 대신 사용자 친화적 메시지 제공 가능.
3. 정렬 안 된 데이터에서 근사 검색: 제품 코드가 정렬되어 있지 않아도 match_mode=‑1 또는 1 조합으로 가장 가까운 값 조회 가능.
4. 역방향 검색: 로그 데이터처럼 최신 항목부터 찾고 싶을 때 search_mode=‑1 사용하면 뒤에서부터 검색해 가장 최근 값 추출.

---

5. 초기 적용 시 주의해야 할 점

• 범위 lookup_array와 return_array의 크기(행 수)가 반드시 동일해야 오류 없음.
• 기본값 if_not_found 없이 사용 시, 일치하지 않을 경우 #N/A 오류 발생 가능.
• match_mode 또는 search_mode 옵션을 잘못 설정하면 의도와 다른 값을 반환할 수 있으니 정확히 지정 필요.
• VLOOKUP에서 넘어가던 자동 숫자 정렬 등을 고려해, 와일드카드 검색(match_mode=2) 사용 시 기존 텍스트 데이터와 충돌 없앱니다.

---

6. 요약 정리

• ✅ XLOOKUP은 VLOOKUP보다 훨씬 유연하고 오류 내성이 강한 최신 조회 함수입니다.
• ✅ 좌우 제한 없이 참조 가능하고, 사용자 지정 오류 처리 및 근사/역방향 매칭 기능을 지원합니다.
• ✅ 테이블 구조와 조합하면 동적 데이터 관리에 강하며, 오류 발생을 최소화할 수 있어 실무에 최적화된 함수입니다.

다음 소주제인 3‑2. 기본 사용법 실습 및 실무 예제에서는 고객 코드 조회, 일치형/근사형 예시, IFERROR 조합 활용 등을 수식 단위로 깊이 있게 분석하겠습니다.