Self-Contained Inline Lookup Mapping with SWITCH & CHOOSE

Keep a small fixed mapping inside a formula

These are fictional order-discount tiers, not Arte Sheets subscription prices or benefits: STD gives 5%, PRO 12% and ENT 20%. Use a maintained lookup table when rules grow or change frequently.

SWITCH selects a result for a matching code and includes an explicit default. This example trims ordinary outer spaces and converts codes to uppercase, so pro and PRO use the same rate. Unknown nonempty text deliberately receives zero discount; this policy may need to be changed to an error for production billing. An empty input cell is rejected as a missing plan code.

CHOOSE selects one result by numeric position. Excel truncates fractional indices, so an explicit integer guard prevents 2.9 from silently selecting the second label. This example makes no complexity or performance guarantee about the spreadsheet engine.

Order discount through SWITCH

B2 must be a nonnegative numeric subtotal. ROUND applies two-decimal rounding to the final illustrative discount only.

=IF(NOT(ISTEXT(A2)),"Enter a text plan code",IF(COUNT(B2)<>1,"Enter numeric subtotal",IF(B2<0,"Subtotal must be nonnegative",ROUND(B2*SWITCH(UPPER(TRIM(A2)),"STD",0.05,"PRO",0.12,"ENT",0.20,0),2))))

Priority label through CHOOSE

C2 must be an actual numeric integer from 1 to 4. Blank, text, fractions and out-of-range values are rejected.

=IF(COUNT(C2)<>1,"Enter numeric priority",IF(OR(C2<1,C2>4,C2<>INT(C2)),"Invalid Tier",CHOOSE(C2,"Low","Normal","High","Critical")))

Further reading

• Microsoft Support — SWITCH function
• Microsoft Support — CHOOSE function and fractional indices