An order of 74 uses the 50-unit rate when one rate applies to the whole order. Store each tier’s minimum separately from its rate.

Look up the exact or next smaller minimum

Enter thresholds 0, 50, and 100 in A2:A4 and rates 5.00, 4.50, and 4.00 in B2:B4. Put 74 in D2. In E2 enter =XLOOKUP(D2,$A$2:$A$4,$B$2:$B$4,"Below first tier",-1). Expect 4.50.

A threshold table shows rates of 5.00, 4.50 and 4.00 at minimums 0, 50 and 100.

Illustrated tier lookup. Choose the largest qualifying minimum.

Check 99 and 100 before relying on the result

At 99 the rate is 4.50, and at 100 it drops to 4.00. A value below 0 returns the fallback text. Keep the thresholds numeric and unique. If the pricing policy charges different rates on portions of one order, calculate those portions separately.

Quantity 99 gives 4.50 and quantity 100 gives 4.00.

Illustrated boundary check. Verify the rate at the first quantity in the new tier.

Microsoft’s XLOOKUP function reference