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.

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.

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



