How to Use INDEX and MATCH to Find a Value Between Two Bounds in Excel
Question details
The user needs to retrieve a specific return value based on an input number falling dynamically between a set of minimum and maximum boundaries.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up a golf-stroke adjustment or similar metric when an input score lands securely within defined 'From' and 'To' data ranges.
- Observed behavior
- Requires a formula capable of evaluating an input against two distinct numeric columns simultaneously and returning a result from a third column.
Verify that your data boundary ranges contain purely numeric values without overlapping intervals, and identify whether your spreadsheet software supports modern dynamic arrays or requires legacy array entry combinations.
Use an INDEX and MATCH Array Formula
This is the most universally compatible method for finding a value between two bounds, working across virtually all spreadsheet software versions.
By multiplying two conditional checks together, we create a temporary array of 1s (True) and 0s (False). The MATCH function then easily searches for the value 1, successfully locating the exact row where both boundaries encapsulate your target number.
Click on the specific cell where you want the resulting matched value to appear.
Type the formula =INDEX(C21:C36,MATCH(1,(C48>=A21:A36)*(C48<=B21:B36),0)), modifying the cell references to match your upper and lower bound columns.
If you are using an older version of Excel (prior to Excel 365), you must press Ctrl+Shift+Enter instead of just Enter to evaluate the logic as an array formula.
Use the XLOOKUP Function (Newer Versions)
A much more straightforward and readable approach exclusively available in newer software like Excel 365 and WPS Office 2024.
Easily Manage Complex Array Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like INDEX, MATCH, and XLOOKUP. You can seamlessly calculate complex data ranges, manage overlapping bounds, and edit Excel files directly without losing any critical formula formatting.
- 1. Install WPS Office: Download and install the free WPS Office suite from the official website.
- 2. Open your workbook: Launch WPS Spreadsheet and open your existing Excel file containing the bounded data.
- 3. Enter the lookup formula: Type your preferred INDEX/MATCH or XLOOKUP formula exactly as you would natively in Excel.
- 4. Calculate instantly: Press Enter to execute the calculation and accurately retrieve your bounded values.
Frequently Asked Questions
Why is my INDEX MATCH array formula returning a #N/A error?
A #N/A error usually occurs if the input value does not fall securely within any of your defined upper and lower bounds, or if the ranges referenced in your formula (like A21:A36 and C21:C36) are not the exact same size. Double-check your boundary alignments.
Do I always need to press Ctrl+Shift+Enter when using INDEX and MATCH?
No. In newer spreadsheet environments equipped with dynamic array support, such as Excel 365 or recent versions of WPS Office, simply pressing Enter is entirely sufficient. However, for Excel 2019 and older, Ctrl+Shift+Enter remains required for arrays.
Can I use the standard VLOOKUP function to find a value between two bounds?
Yes, but strictly if your boundary table is sorted in ascending order based on the lower bound column. You can then use VLOOKUP with an approximate match (setting the final argument to TRUE or 1). However, the INDEX and MATCH array method offers higher precision since it doesn't force you to sort your data.
