VLOOKUP Maximum and Minimum: Threshold Matching and Category-Based Max/Min
Solve VLOOKUP maximum minimum problems in 10 seconds. For the maximum (≤) at or below a threshold, use VLOOKUP (approximate match); for the minimum (≥) at or above a threshold, use XLOOKUP or INDEX+MATCH(-1). For category-based maximum/minimum values and returning related values (such as item names), use MAXIFS/MINIFS + XMATCH.
Quick Fix
- Maximum ≤ (Column A sorted ascending):
=VLOOKUP(H2, A2:C100, 3, TRUE) - Minimum ≥ (no sorting required; recommended):
=XLOOKUP(H2, A2:A100, C2:C100, , 1) - Maximum value by category:
=MAXIFS(C2:C100, A2:A100, K2) - Item for that value:
=LET(m,MAXIFS(C2:C100,A2:A100,K2), INDEX(B2:B100, XMATCH(1,(A2:A100=K2)*(C2:C100=m))))
Concept: VLOOKUP Approximate Match and Sorting
When the lookup column is sorted in ascending order, VLOOKUP’s TRUE approximate match selects the largest value that is ≤ the threshold. Conversely, to find the smallest value ≥ the threshold, use XLOOKUP with match_mode=1 or MATCH with -1 (with the lookup column sorted in descending order).
Practical Example ① Threshold Matching
| A (Lookup Value) | B (Grade) | C (Benefit) |
|---|---|---|
| 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)Practical Example ② Category-Based Maximum/Minimum + Related Value
| 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))))Return all ties (Microsoft 365):
=FILTER(B2:B6, (A2:A6=F2)*(C2:C6=MAXIFS(C2:C6, A2:A6, F2)))Alternatives and Extensions
- XLOOKUP: Use
match_mode±1 andsearch_mode±1 to control the first or last match. - INDEX+MATCH/XMATCH: Use the sort direction to adapt for ≥ or ≤ matching.
- FILTER·SORTBY: Handle top N results and ties.
Checklist & Notes
VLOOKUP(...,TRUE)requires the lookup column to be sorted in ascending order.- For the minimum ≥ value, use
XLOOKUP(...,1)orMATCH(-1)with descending sort order. - To find the row from a category-based value, use
MAXIFS/MINIFS+XMATCH/INDEX. - Decide how to handle ties: first, last, or all.
- Clean up mixed numbers and text before calculating.
Troubleshooting
| Symptom | Cause | Solution |
|---|---|---|
| Incorrect approximate-match result | Not sorted | Sort the lookup column in ascending order or switch to XLOOKUP. |
| Difficulty finding the minimum ≥ value | VLOOKUP structural limitation | Use XLOOKUP with match_mode=1. |
| Which row is returned for ties? | Duplicate maximum/minimum values | Use XMATCH for the first match or FILTER to return all matches. |
| Match fails | Mixed numbers/text or spaces | Clean the data with TRIM·CLEAN. |