XMATCH + INDEX로 100만 행에서도 빠른 근사 조회 만들기(정렬·이진검색)
XMATCH INDEX 근사 조회 조합은 대용량에서도 빠르고 투명합니다. XMATCH의 이진검색으로 경계 인덱스를 정확히 잡고 INDEX로 값을 꺼내면 VLOOKUP의 취약점이 사라집니다.
Quick Fix — “느려요/틀려요”를 바로 잡는 6단계
- 기준열 오름차순 정렬 확인
- 하한은
-1, 상한은1선택 INDEX(XMATCH())분리 설계- 표 개체/절대참조로 범위 고정
FLOOR.MATH/CEILING.MATH로 버킷 스냅- 중복 경계 정책 +
search_mode적용
왜 XMATCH + INDEX인가?
이진검색 기반 match_mode
=XMATCH(값, 기준범위, -1) // 다음 작은(≤)
=XMATCH(값, 기준범위, 1) // 다음 큰(≥)분리 설계의 장점
INDEX는 결과범위가 독립이라 열 이동에도 안정적이며, XMATCH 결과를 보조열로 분리하면 테스트가 쉬워집니다.
근사 조회 패턴
하한 기준
=INDEX($B$2:$B$6, XMATCH(E2, $A$2:$A$6, -1))상한 기준
=INDEX($B$2:$B$6, XMATCH(E2, $A$2:$A$6, 1))중복 경계 우선순위
=INDEX($B$2:$B$6, XMATCH(E2, $A$2:$A$6, -1, -1))100만 행 성능 설계
- 정렬 보증: 숫자/날짜 형식 통일, 표 개체 사용
- 범위 최소화·보조열 캐시·스필로 중복 계산 제거
- .xlsb 저장 등으로 파일 크기 절감
실무 예제 1 — 점수→등급
| 하한점수 | 등급 |
|---|---|
| 0 | D |
| 60 | C |
| 70 | B |
| 80 | A |
| 90 | A+ |
=INDEX($B$2:$B$6, XMATCH(E2, $A$2:$A$6, -1))
경계 스냅: =INDEX($B$2:$B$6, XMATCH(FLOOR.MATH(E2,1), $A$2:$A$6, -1))
실무 예제 2 — 사용량→단가(누진)
| 사용량 하한 | 단가(원) |
|---|---|
| 0 | 100 |
| 100 | 90 |
| 300 | 80 |
| 500 | 70 |
단가 조회: =INDEX($B$2:$B$5, XMATCH(G2, $A$2:$A$5, -1))
누진 합계: 구간사용량 계산 후 =SUMPRODUCT(구간사용량범위, 단가범위)
대체 방법/주의사항/체크리스트
- XLOOKUP 일체형보다 INDEX+XMATCH가 디버깅에 유리
- 버킷 스냅·가상 하한/상한으로 예외 처리
- [ ] 정렬 보증
- [ ] 데이터 형식 통일
- [ ] -1/1 의도 명시
- [ ] 중복 정책 + 검색방향
- [ ] 버킷 스냅
- [ ] 범위 외 예외 처리
맺음말 & 관련 글
공식 문서: XMATCH Function, INDEX Function