VLOOKUP maximum minimum — 임계값 매칭과 범주별 최대·최소 완전정리
VLOOKUP maximum minimum 문제를 10초에 해결합니다. 임계값 이하의 최대(≤)는 VLOOKUP(근사), 임계값 이상의 최소(≥)는 XLOOKUP 또는 INDEX+MATCH(-1)로 처리하고, 범주별 최대/최소와 관련 값(상품명 등) 반환은 MAXIFS/MINIFS + XMATCH 조합으로 해결합니다.
Quick Fix
- ≤ 최대 (A열 오름차순 정렬):
=VLOOKUP(H2, A2:C100, 3, TRUE) - ≥ 최소 (정렬 불요, 권장):
=XLOOKUP(H2, A2:A100, C2:C100, , 1) - 범주별 최대 값:
=MAXIFS(C2:C100, A2:A100, K2) - 그 값의 항목:
=LET(m,MAXIFS(C2:C100,A2:A100,K2), INDEX(B2:B100, XMATCH(1,(A2:A100=K2)*(C2:C100=m))))
개념: VLOOKUP 근사와 정렬
VLOOKUP의 TRUE 근사매칭은 기준열이 오름차순일 때 “≤ 임계값 중 최댓값”을 선택합니다. 반대로 “≥ 임계값 중 최솟값”은 XLOOKUP의 match_mode=1 또는 MATCH의 -1(기준열 내림차순)로 구현합니다.
실무 예제 ① 임계값 매칭
| A(기준) | B(등급) | C(혜택) |
|---|---|---|
| 60 | Bronze | 1% |
| 80 | Silver | 3% |
| 95 | Gold | 5% |
| 110 | Platinum | 7% |
=VLOOKUP(97, A2:C5, 2, TRUE) =XLOOKUP(97, A2:A5, B2:B5, , 1)실무 예제 ② 범주별 최대/최소 + 관련 값
| Category | Item | Price |
|---|---|---|
| A | Alpha | 12 |
| A | Apex | 19 |
| B | Beta | 18 |
| B | Bolt | 18 |
| B | Brave | 22 |
=MAXIFS(C2:C6, A2:A6, F2)=LET(m,MAXIFS(C2:C6,A2:A6,F2), INDEX(B2:B6, XMATCH(1,(A2:A6=F2)*(C2:C6=m))))동점 모두 반환(365):
=FILTER(B2:B6, (A2:A6=F2)*(C2:C6=MAXIFS(C2:C6, A2:A6, F2)))대체·확장
- XLOOKUP:
match_mode±1,search_mode±1로 첫/마지막 제어 - INDEX+MATCH/XMATCH: 정렬 방향으로 ≥/≤ 변형
- FILTER·SORTBY: 상위 N, 동점 처리
체크리스트 & 주의사항
VLOOKUP(...,TRUE)는 기준열 오름차순 필수- ≥ 최소는
XLOOKUP(...,1)또는MATCH(-1)+내림차순 - 범주별 값→행 찾기:
MAXIFS/MINIFS+XMATCH/INDEX - 동점 정책을 결정(첫/마지막/모두)
- 숫자/텍스트 혼합은 정리 후 계산
Troubleshooting
| 증상 | 원인 | 해결 |
|---|---|---|
| 근사매칭 값이 틀림 | 정렬 안 됨 | 기준열 오름차순 정렬 또는 XLOOKUP으로 변경 |
| ≥ 최소 구현 어려움 | VLOOKUP 구조 한계 | XLOOKUP match_mode=1 사용 |
| 동점 중 어떤 행? | 중복 최대/최소 | XMATCH로 첫 번째, FILTER로 모두 반환 |
| 일치 실패 | 숫자/텍스트 혼재, 공백 | TRIM·CLEAN으로 정리 |