How to Apply Discount Rules with SWITCH in Excel

Look up tier and promo-code discounts with SWITCH, calculate net prices, and give each customer the better deal.

You will learn to

• Return a discount rate for a customer tier
• Calculate a net price from the rate
• Compare two discount rules and apply the better one

How it works

When discounts depend on exact labels — a loyalty tier or a promo code — SWITCH keeps the rules in one readable place: =SWITCH(B2,"Gold",0.15,"Silver",0.1,"Bronze",0.05,0). The final 0 is the default for customers without a tier. Multiply the order value by (1 − rate) to get the net price.

When two discounts can apply but must not stack, compute both with SWITCH and keep the larger with MAX: =C3*(1-MAX(SWITCH(B3,…),SWITCH(D3,"SPRING20",0.2,"WELCOME10",0.1,0))). Customers automatically get the better deal.

=SWITCH(B2,"Gold",0.15,"Silver",0.1,"Bronze",0.05,0)