Lookup & Reference
Group Numbers into Pricing or Loyalty Tiers (Approximate VLOOKUP vs XLOOKUP -1)
Bucket continuous revenue, score, or age figures into named brackets without deep nested IF chains—comparing classic sorted VLOOKUP(..., TRUE) against sort-independent XLOOKUP(..., -1).
Why lookup tier tables beat 6-level nested IF chains—and one VLOOKUP trap to watch
Replacing IF(B2>=15000,"Platinum",IF(B2>=5000,"Gold",...)) with a visible 2-column tier table puts threshold governance right on the grid where finance teams can audit and adjust breakpoints without editing formula syntax.
Classic VLOOKUP(B2, $E$2:$F$5, 2, TRUE) requires $E$2:$E$5 to be sorted strictly ascending and throws #N/A for values below the lowest threshold (such as negative credit memos when the table starts at 0). Modern XLOOKUP(B2, $E$2:$E$5, $F$2:$F$5, "BELOW_MIN", -1) matches the exact-or-next-smaller breakpoint regardless of table sort order and returns an explicit fallback below 0.
| Method | Requires Ascending Sort? | Below-Minimum Behavior | Formula |
|---|---|---|---|
| VLOOKUP (range_lookup = TRUE) | Yes (mandatory) | #N/A error | VLOOKUP(B2, $E$2:$F$5, 2, TRUE) |
| XLOOKUP (match_mode = -1) | No | Custom if_not_found text | XLOOKUP(B2, $E$2:$E$5, $F$2:$F$5, "BELOW_MIN", -1) |
Approximate Tier Assignment Formula
Pair XLOOKUP match_mode = -1 (exact match or next smaller item) with a 0-anchored tier schedule.
=XLOOKUP(B2, $E$2:$E$5, $F$2:$F$5, "BELOW_MIN", -1)Try the example
Use the original values or change them to test your understanding.