How to Calculate Price Tiers from Min and Max Thresholds in Excel
Question details
The user needs to assign a price tier and calculate a rate based on an input value that falls between specific minimum and maximum thresholds.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating dynamic pricing or markups where an input value must be evaluated against both lower and upper limits in a lookup table.
- Observed behavior
- The user wants to retrieve the correct tier and rate from a table by evaluating both minimum and maximum threshold columns simultaneously.
Ensure your pricing tier table is organized with separate columns for Minimum, Maximum, Tier Name, and Rate, and that the data is sorted logically.
Use XLOOKUP with Multiple Criteria for Price Tiers
Use the XLOOKUP function combined with boolean logic to evaluate both minimum and maximum thresholds simultaneously.
By multiplying two conditions (Value>=Min)*(Value<=Max), Excel creates an array of 1s and 0s. XLOOKUP then searches for the value 1 to find the exact row where both conditions are met.
Create a reference table with separate columns for 'Min', 'Max', 'Tier', and 'Rate'.
Click on the cell where you want the Tier result to appear.
Type the formula =XLOOKUP(1,(H3>=MinRange)*(H3<=MaxRange),TierRange,"Not found"), replacing H3 with your input cell and the ranges with your actual table columns.
To get the corresponding rate, use =XLOOKUP(1,(H3>=MinRange)*(H3<=MaxRange),RateRange,0) in the adjacent cell.
Multiply your input amount by the retrieved rate to calculate the final markup or price.
Use WPS Spreadsheet for Advanced Lookup Functions
WPS Spreadsheet fully supports advanced functions like XLOOKUP and array formulas, making it easy to calculate dynamic price tiers and complex lookups.
- 1. Open your workbook: Launch WPS Spreadsheet and open your pricing workbook.
- 2. Organize your data: Ensure your tier data is organized into Min, Max, and Rate columns.
- 3. Apply the XLOOKUP formula: Type =XLOOKUP(1, (Input>=MinRange)*(Input<=MaxRange), ResultRange) to fetch the correct tier or rate.
- 4. Fill the formula: Press Enter to apply the formula and drag the fill handle down to apply it to other cells.
Frequently Asked Questions
Can I use VLOOKUP instead of XLOOKUP for price tiers?
Yes, you can use VLOOKUP with an approximate match (set the last argument to TRUE or 1). However, your lookup table must be sorted in ascending order by the minimum threshold, and you only need the 'Min' column, not a 'Max' column.
Why does my XLOOKUP formula return an error?
This usually happens if the ranges in your formula are not the same size. Ensure that your MinRange, MaxRange, and ResultRange all cover the exact same number of rows.
How do I handle values that fall outside my defined thresholds?
You can use the 'if_not_found' argument in XLOOKUP. For example, =XLOOKUP(1, (Criteria), ResultRange, "Out of Range") will display 'Out of Range' instead of an #N/A error.
Does this boolean logic work in older versions of Excel?
XLOOKUP is only available in Microsoft 365, Excel 2021, and modern alternatives like WPS Office. For older versions, you can use an INDEX and MATCH array formula: =INDEX(TierRange, MATCH(1, (H3>=MinRange)*(H3<=MaxRange), 0)) entered with Ctrl+Shift+Enter.
