Lookup & Reference
Match Next Highest Capacity Bracket & Dynamic Column Peak (XLOOKUP +1 & CHOOSECOLS)
Select the smallest server, container, or breaker rating that meets or exceeds required load using XLOOKUP match_mode = 1, and verify dynamic column capacity peaks with MAX(CHOOSECOLS(...)).
Engineering & capacity sizing requires ceiling matching (match_mode = 1), not floor (-1)
Pricing discounts look downward (match_mode = -1: 75 units gets the 50-unit price, not the 100-unit price). Engineering capacity sizing works in the exact opposite direction: a 62 kW load cannot run on a 50 kW generator—it must step UP to the smallest available unit >= 62 kW (for example, 75 kW), and return "EXCEEDS_MAX" if load surpasses the largest rated model.
Setting XLOOKUP's 5th argument match_mode = 1 finds an exact match or the next larger capacity regardless of sort order, while MAX(CHOOSECOLS(matrix, MATCH(col_name, headers, 0))) retrieves the maximum rated capacity of any selected voltage or spec column.
| Sizing Task | Formula Pattern | Behavior on 62 kW Load |
|---|---|---|
| Next-highest rated model | XLOOKUP(62, $D$2:$D$5, $E$2:$E$5, "EXCEEDS_MAX", 1) | Selects 75 kW model ("GEN-75") |
| Headroom kW margin | XLOOKUP(62, $D$2:$D$5, $D$2:$D$5, NA(), 1) - 62 | 13 kW spare capacity |
| Selected column max peak | MAX(CHOOSECOLS($D$2:$F$5, MATCH(hdr, $D$1:$F$1, 0))) | Returns peak rating of chosen spec column |
Next-Highest Capacity Lookup & Dynamic Column Max Formula
Use match_mode = 1 in XLOOKUP for ceiling bracket selection and CHOOSECOLS + MATCH for dynamic column maximums.
=XLOOKUP(B2, $E$2:$E$5, $D$2:$D$5, "EXCEEDS_MAX", 1)Try the example
Use the original values or change them to test your understanding.