XLOOKUP vs VLOOKUP vs INDEX/MATCH — 비교·마이그레이션 완전 가이드
편집자가 Excel 365에서 직접 재현·검증한 예제로 구성되었으며, 초안 정리에 AI 도구를 보조적으로 사용했습니다.
선행/연계 가이드
1) 핵심 비교 표 — xlookup vlookup 비교
| 항목 | XLOOKUP | VLOOKUP | INDEX/MATCH |
|---|---|---|---|
| 왼/오 조회 | 가능(양방향) | 오른쪽만 | 가능 |
| 열 삽입 안전성 | 높음(반환열 직접 지정) | 낮음(col_index 수동) | 중간 |
| 기본 일치 모드 | 정확히(0) | 근사(TRUE) 실수 잦음 | 정확히 |
| 에러 처리 | 4번째 인수에 기본값 | IFERROR로 감싸야 | IFERROR 권장 |
| 와일드카드 | 지원(“*”,”?”) | 지원 | MATCH(2)로 일부 |
| 속도/대역폭 | 대체로 우수 | 보통 | 대용량에 최적화 시 강력 |
2) 대표 패턴 8가지(복붙용)
① 기본
=XLOOKUP(E2, A:A, B:B, "없음", 0)② 왼쪽으로 조회
=XLOOKUP(E2, C:C, B:B, "없음") // 반환열이 왼쪽에 있어도 OK③ 부분 일치(와일드카드)
=XLOOKUP("*"&E2&"*", A:A, B:B, "없음")④ 마지막 값(뒤에서부터)
=XLOOKUP(E2, A:A, B:B, "없음", 0, -1)⑤ 범위 밖 기본값 + 에러 방어
=XLOOKUP(E2, A:A, B:B, "미등록") // IFERROR 불필요⑥ 행+열 양방향(2D)
=XLOOKUP(H2, A:A, XLOOKUP(I2, A1:Z1, A2:Z100))⑦ 다중 조건(AND)
=XLOOKUP(1, (A:A=E2)*(B:B=F2), C:C, "없음")⑧ 근사값(가격표/코드 범위)
=XLOOKUP(E2, A:A, B:B, "없음", 1) // 오름차순 기준 근사값3) 다중 조건/양방향/근사값 — 실무 예제
다중 조건 + 반환 여러 열(스필)
=XLOOKUP(1,(tbl[품목]=E2)*(tbl[지역]=F2), CHOOSECOLS(tbl,3,4,5), "없음")양방향: 행(고객) + 열(월)
=XLOOKUP(E2, 고객, XLOOKUP(F2, 월헤더, 데이터범위))근사값 + 경계 검증
=LET(x, XLOOKUP(E2, 기준구간, 요율, , 1), IF(x="", "구간없음", x))TIP 문자열 전처리는 TEXTSPLIT/BEFORE/AFTER, 목록 정리는 FILTER·SORT·UNIQUE를 사용하면 정확도가 올라갑니다.
4) VLOOKUP → XLOOKUP 교체 체크리스트
- 열 삽입에 취약한 col_index_num 제거 → 반환열 직접 참조로 안정화
- 기본 일치 모드를 정확히(0)로 통일해 오류 감소
- 에러 메시지는 4번째 인수 기본값으로 표준화(예: “없음”)
- 뒤에서 검색해야 할 땐 검색 모드=-1
- 다중 조건은 곱셈식(AND) 또는 CHOOSE로 키 결합
// VLOOKUP(E2, A:D, 4, FALSE)
// ↓
=XLOOKUP(E2, A:A, D:D, "없음", 0)5) 자주 발생하는 오류와 해결
- 숫자처럼 보이는 텍스트:
NUMBERVALUE또는 Power Query로 형식 정리 - 공백/제어문자:
TRIM+SUBSTITUTE(,CHAR(160), " ") - 키 중복:
UNIQUE로 진단 → 필요 시XMATCH와 결합 - #N/A 다발: 철자·대소문자·공백·유니코드 공백 확인, IFERROR로 우회 메시지 제공
6) 요약
- 권장 기본값:
=XLOOKUP(key, 찾을열, 반환열, "없음", 0) - 다중 조건:
XLOOKUP(1,(조건1)*(조건2), 반환) - 양방향: XLOOKUP 안에 XLOOKUP 중첩
- 근사값: 정렬 후
match_mode=1또는-1