How to Return a Result When a Number Falls Within a Range in Excel
Question details
The user needs to look up and return a specific label from a column when a target value falls between a minimum and maximum limit specified in other columns.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Categorizing data, assigning grades, or applying pricing tiers based on a numeric value falling within specific upper and lower boundaries.
- Observed behavior
- The goal is to automatically output the correct category label by evaluating whether a target value is greater than or equal to a minimum limit and less than or equal to a maximum limit without overlapping errors.
Ensure your minimum and maximum limits are clearly separated into two distinct columns and verify that your numerical ranges do not overlap.
Use INDEX and MATCH for Exact Range Boundaries
This method checks both the minimum and maximum columns simultaneously using Boolean logic to return the exact match.
By multiplying two logical conditions, Excel creates an array of 1s and 0s. The MATCH function then searches for the value 1 to locate the row where both conditions are true.
Click on the cell where you want the resulting label to appear.
Type the formula: =INDEX(D:D,MATCH(1,(A2>=E:E)*(A2<=F:F),0)). In this formula, column D contains the labels, A2 is your target number, column E is the minimum limit, and column F is the maximum limit.
Press Enter to execute. Drag the fill handle down to apply the formula to other rows if necessary.
Use LOOKUP Function for an Alternative Approach
The LOOKUP function provides a slightly shorter syntax to achieve the same Boolean range check without needing MATCH.
Use XLOOKUP for Sorted Lower Boundaries
If your data is sorted by the minimum value and you only need to check the lower boundary, XLOOKUP is a modern and efficient solution.
Seamlessly Look Up Data Ranges with WPS Spreadsheet
WPS Office provides robust formula support, including fully functional INDEX, MATCH, and XLOOKUP functions, allowing you to easily categorize and extract data within specified ranges.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data ranges.
- 2. Select the output cell: Click on the specific cell where you want the matched label to be displayed.
- 3. Input your lookup formula: Type your preferred formula, such as =INDEX(D:D,MATCH(1,(A2>=E:E)*(A2<=F:F),0)).
- 4. Execute the calculation: Press Enter (or Ctrl+Shift+Enter for array formulas) to instantly categorize your data based on the defined limits.
Frequently Asked Questions
Why does my INDEX and MATCH formula return an #N/A error?
This usually happens if the target number does not fall within any of the specified ranges, if there are typos in your ranges, or if you forgot to use Ctrl+Shift+Enter to evaluate the array formula in older software versions.
Can I use VLOOKUP instead of INDEX and MATCH for number ranges?
Yes, you can use VLOOKUP with the approximate match argument set to TRUE. However, your reference table must be strictly sorted in ascending order by the lower boundary, and it does not check a secondary maximum column.
How do I prevent overlapping ranges from causing incorrect lookup results?
Ensure the maximum limit of one tier is strictly less than the minimum limit of the next tier. For example, use 0-10 and 11-20 instead of 0-10 and 10-20 to avoid ambiguity when the lookup value is exactly 10.
