Logical & Lookup Formulas
Map Text Tiers to Arbitrary Numeric Weights (CHOOSE & MATCH vs XLOOKUP)
Assign non-linear SLA severity weights or T-shirt story points ("XS"->1, "S"->2, "M"->5, "L"->8, "XL"->13) to text categories without nested IFs using CHOOSE(MATCH(...)) or XLOOKUP.
Why Fibonacci story points and SLA weights outgrow nested IFs immediately!
Agile sprint planners mapping T-shirt sizes ("XS", "S", "M", "L", "XL") onto Fibonacci story points (1, 2, 5, 8, 13)—or incident commanders scoring ticket severities ("P0" -> 25, "P1" -> 10, "P2" -> 3, "P3" -> 1)—cannot multiply a linear sequence by a constant! Writing five nested IF statements quickly turns brittle and unreadable.
Two clean patterns replace nested IF spaghetti: (1) self-contained inline =IFERROR(CHOOSE(MATCH(B2, {"P0","P1","P2","P3"}, 0), 25, 10, 3, 1), 0) when you want zero external lookup cells, or (2) table-driven =XLOOKUP(B2, $E$2:$E$5, $F$2:$F$5, 0) paired with =SUMPRODUCT(COUNTIF(B2:B7, E2:E5), F2:F5) when managers want to tune weights live on the sheet!
| Incident Severity Code | MATCH Index (1–4) | Arbitrary SLA Weight Assigned | Open Queue Example |
|---|---|---|---|
| "P0" (Production Outage) | 1 | 25 pts | 1 ticket × 25 = 25 pts |
| "P1" (Major Degradation) | 2 | 10 pts | 2 tickets × 10 = 20 pts |
| "P2" / "P3" (Minor / Cosmetic) | 3 / 4 | 3 pts / 1 pt | 2×3 + 1×1 = 7 pts (Total = 52) |
Inline Arbitrary Weight Mapper via CHOOSE(MATCH(...))
Matches category B2 against {"P0","P1","P2","P3"} and returns arbitrary non-linear weight 25, 10, 3, or 1 (defaulting unknown labels to 0).
=IFERROR(CHOOSE(MATCH(B2, {"P0","P1","P2","P3"}, 0), 25, 10, 3, 1), 0)Lookup-Table Reference Mapper via XLOOKUP
Maps B2 against an auditable on-sheet tier table E2:F5 so stakeholders can adjust weights without editing formulas.
=XLOOKUP(B2, $E$2:$E$5, $F$2:$F$5, 0)Try the example
Use the original values or change them to test your understanding.