Identifying Discount Tier for Purchase Orders
A procurement analyst needs to quickly determine which discount tier a specific purchase order value falls into. The discount tiers are based on ascending minimum purchase value thresholds, and the goal is to find the position of the *highest* threshold that is *less than or equal to* the purchase value to apply the correct discount.
=MATCH(1250,A2:A5,1) How it works: This MATCH function is used to find the position of the appropriate discount tier. `1250` is the `lookup_value` (the purchase amount), `A2:A5` is the `lookup_array` (the sorted minimum purchase values), and `1` (less than or equal to) is the `match_type`. Excel finds the largest value in `A2:A5` that is less than or equal to 1250, which is 1000. It then returns its relative position, `3`, indicating the third tier.
Data Setup
| MinPurchaseValue | DiscountPercentage |
|---|---|
| 0 | 0% |
| 500 | 5% |
| 1000 | 10% |
| 2000 | 15% |
Step-by-Step Guide
Ensure your `MinPurchaseValue` thresholds are sorted in ascending order (e.g., 0, 500, 1000, 2000).
Identify the purchase order value you want to evaluate (e.g., 1250).
Determine the range containing your sorted `MinPurchaseValue` thresholds (e.g., A2:A5).
Enter the MATCH function: `=MATCH(1250,A2:A5,1)` into an empty cell.
The `1` as the third argument tells MATCH to find the largest value that is less than or equal to the `lookup_value`.