Find a Category by Number Range with Excel INDEX and MATCH
Question details
Return the correct TRS, category, or other result by checking whether a number falls between a row's low and high range limits.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- An address block number in one cell must be matched against minimum and maximum values in a lookup table, then return the corresponding category.
- Observed behavior
- The required result is the value from the result column for the row where the block number is greater than or equal to the low limit and less than or equal to the high limit.
Ensure the low-limit and high-limit columns contain numeric values and that each range has a corresponding result in the return column. Use matching row ranges rather than whole-column references to improve calculation performance.
Use INDEX and MATCH with low and high range conditions
Use this formula when a lookup value must fall within a minimum and maximum number range before Excel returns the matching category.
This formula multiplies two logical tests: one verifies that the block number is at least the low limit, and the other verifies that it is no greater than the high limit. MATCH finds the row where both tests are true, and INDEX returns the related value.
Place the address block number to search for in cell A2. In the lookup table, use E2:E100 for low limits, F2:F100 for high limits, and H2:H100 for the TRS or category to return.
Select the result cell and enter: =INDEX(H2:H100,MATCH(1,(A2>=E2:E100)*(A2<=F2:F100),0))
Press Enter in Microsoft 365 and newer Excel versions. In older Excel versions, confirm it as an array formula by pressing Ctrl+Shift+Enter.
Drag the fill handle down from the result cell to apply the lookup to additional address block numbers.
Use XLOOKUP when the range table is sorted by low limit
Use this alternative in newer Excel versions when the low-limit column is sorted in ascending order and you want a shorter formula.
Find the Correct Category for a Number Range in WPS Spreadsheet
WPS Spreadsheet can use INDEX and MATCH to return a category when a number falls between low and high limits. Enter the formula in a result cell, reference equal-sized lookup ranges, and fill it down for additional records.
- 1. Open the worksheet in WPS Spreadsheet: Open the Excel workbook in WPS Office and select the worksheet containing the block number and range table.
- 2. Select the output cell: Click the cell where the TRS or category should appear, such as B2.
- 3. Enter the INDEX and MATCH formula: Type =INDEX(H2:H100,MATCH(1,(A2>=E2:E100)*(A2<=F2:F100),0)) and adjust the cell ranges to match your table.
- 4. Confirm and fill down: Press Enter to calculate the result, then drag the fill handle down to return categories for more input numbers.
Frequently Asked Questions
What does the multiplication sign do in the INDEX and MATCH range formula?
The expression (A2>=E2:E100)*(A2<=F2:F100) converts the two tests into 1 or 0 values. Only a row where both tests are true produces 1, which MATCH searches for.
Why does my INDEX and MATCH range lookup return #N/A?
Check that the value in A2 is numeric, the low and high limits are numeric, and at least one row satisfies both A2>=low limit and A2<=high limit. Also ensure the INDEX return range and both MATCH ranges start and end on the same rows.
Do I need Ctrl+Shift+Enter for this formula?
In older Excel versions, yes: confirm the formula with Ctrl+Shift+Enter because it evaluates arrays. Microsoft 365 and newer versions generally calculate it after you press Enter.
Can I use entire columns in the formula?
You can use references such as H:H, E:E, and F:F, but fixed ranges such as H2:H100 are usually faster and avoid unnecessary calculation across all worksheet rows.
What happens if a number appears in two ranges?
MATCH returns the first row that meets both conditions. To avoid unexpected results, sort and maintain the lookup table so its low-to-high ranges do not overlap.
