근사 모드 VLOOKUP vs XLOOKUP 근사 — 경계값 함정 완벽 정리
등급표·요금제·세율처럼 “구간” 기준으로 값을 찾을 때, VLOOKUP 근사 모드(TRUE)나 XLOOKUP 근사를 쓰면 경계값(임계값)에서 틀린 값이 튀는 일이 잦습니다. 이 글은 원리를 짚고, VLOOKUP 근사 XLOOKUP 근사 비교로 경계값 함정을 방지하는 실무 레시피를 제공합니다.
Quick Fix — 당장 틀린 값부터 잡는 5단계
- 기준표 정렬 확인: VLOOKUP 근사(TRUE)는 오름차순 정렬 필수.
- XLOOKUP으로 전환:
=XLOOKUP(값, 기준, 결과, "", -1)또는1. - 경계 안전장치:
FLOOR.MATH/CEILING.MATH로 버킷 스냅. - 결측 구간 검사: 조건부 서식으로 빈 구간 탐지.
- 중복 경계 처리:
search_mode로 첫/마지막 정의 선택.
VLOOKUP 근사 모드의 원리(왜 틀려 보이나?)
TRUE/1 모드와 오름차순 정렬의 의미
=VLOOKUP(찾을값, 표, 열, TRUE)는 찾을값 이하에서 가장 큰 값을 찾습니다. 기준열은 반드시 오름차순.
경계값이 비어 있거나 중복될 때
빈 구간이 존재하면 경계 사이 값이 잘못된 구간으로 들어갈 수 있고, 중복 경계는 예측 불가능한 선택을 유발합니다.
데이터가 섞여 있을 때(정렬 불량)
하나라도 어긋난 행이 있으면 근사 검색은 그 위치에서 멈추며 엉뚱한 결과를 줍니다.
XLOOKUP 근사 모드 제대로 쓰기(안전한 기본기)
match_mode -1 vs 1
=XLOOKUP(값, 기준범위, 결과범위, "없음", -1) // 다음 작은 값(≤)
=XLOOKUP(값, 기준범위, 결과범위, "없음", 1) // 다음 큰 값(≥)검색 방향(search_mode)
=XLOOKUP(값, 범위, 결과, , -1, 1) // 위→아래
=XLOOKUP(값, 범위, 결과, , -1, -1) // 아래→위XMATCH + INDEX
=INDEX(결과범위, XMATCH(값, 기준범위, -1))
=INDEX(결과범위, XMATCH(값, 기준범위, 1))실무 예제 1 — 등급표(점수→등급)
| 하한점수 | 등급 |
|---|---|
| 0 | D |
| 60 | C |
| 70 | B |
| 80 | A |
| 90 | A+ |
XLOOKUP -1: =XLOOKUP(E2, A2:A6, B2:B6, "등급없음", -1)
버킷 스냅: =XLOOKUP(FLOOR.MATH(E2,1), A2:A6, B2:B6, "등급없음", -1)
마지막 정의 우선: =XLOOKUP(E2, A2:A6, B2:B6, "등급없음", -1, -1)
실무 예제 2 — 누진 요율/요금제
| 사용량 하한 | 단가(원) |
|---|---|
| 0 | 100 |
| 100 | 90 |
| 300 | 80 |
| 500 | 70 |
구간 단가: =XLOOKUP(G2, A2:A5, B2:B5, "", -1)
누진 합계: 구간사용량 계산 후 =SUMPRODUCT(구간사용량범위, 단가범위)
대체 레시피/주의사항/체크리스트
- 범위 외 값은 “없음” 처리 또는 가상 하한/상한 행 추가
- 가능하면 오름차순 유지(개념 혼선 감소)
- [ ] 오름차순 정렬
- [ ] 빈 구간 없음
- [ ] 중복 경계 처리 방침
- [ ] -1/1 선택 검증
- [ ] FLOOR/CEILING 적용
- [ ] 범위 외 예외 처리
Troubleshooting
| 증상 | 원인 | 해결법 |
|---|---|---|
| 경계에서 한 단계 어긋남 | match_mode 선택 오류 | -1/1 재선택 |
| 중복 경계 오답 | 중복 + 기본 검색방향 | search_mode=-1 또는 중복 제거 |
| 엉뚱한 구간 | 정렬 불량 | 오름차순 재정렬 |
| “없음” 다수 | 범위 외/빈 구간 | 가상 경계 추가/구간 보정 |
| 소수점 출렁임 | 스냅 미적용 | FLOOR/CEILING 적용 |
마무리 & 추천 글
핵심은 정렬·연속성·중복·스냅입니다. XLOOKUP의 -1/1을 명확히 쓰고, 필요하면 XMATCH+INDEX로 투명하게 관리하세요.
공식 문서 참고: XLOOKUP Function, VLOOKUP Function