How to Lookup a Value Between Low and High Limits in Excel
Question details
The user needs to retrieve a specific code from a table by determining if a search value falls precisely between explicitly defined lower and upper limits.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Assigning a classification code to a number when the lookup table has three columns: a low boundary, a high boundary, and the corresponding code.
- Observed behavior
- A standard VLOOKUP only checks the first column (lower limit) for an approximate match, failing to properly validate if the number actually exceeds the upper limit defined in the second column.
Ensure your lookup data is organized into three distinct columns: lower limits, upper limits, and the result codes, and that your search values are ready in a separate cell.
Use INDEX and MATCH Array Formula
This method uses an array formula to evaluate both the lower limit and upper limit columns simultaneously, ensuring strict adherence to the defined boundaries.
By multiplying two logical conditions inside the MATCH function, Excel creates an array of 1s (True) and 0s (False). The MATCH function then searches for the value 1, pinpointing the exact row where the search value is both greater than or equal to the lower limit and less than or equal to the upper limit.
Place your lower limits in A2:A5, upper limits in B2:B5, and the output codes in C2:C5. Assume the value you want to search for is typed into cell E2.
Click on the cell where you want the result to appear (e.g., F2) and input the following formula: =INDEX($C$2:$C$5,MATCH(1,(E2>=$A$2:$A$5)*(E2<=$B$2:$B$5),0))
If you are using an older version of Excel, you must press Ctrl+Shift+Enter to evaluate it as an array formula (curly brackets will appear around it). In newer versions of Excel or WPS Spreadsheet, simply press Enter.
Use VLOOKUP for Contiguous Sorted Ranges
If your boundary ranges leave no gaps (the upper limit of one tier is the lower limit of the next) and are sorted in ascending order, a simple VLOOKUP is sufficient.
Perform Complex Range Lookups Effortlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas like INDEX and MATCH, allowing you to solve complex data classification and lookup problems with ease.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new blank spreadsheet or open your existing workbook.
- 2. Input Your Data: Format your table with lower limits, upper limits, and codes in separate columns.
- 3. Apply the Array Formula: Select the target cell, paste the INDEX and MATCH formula, and press Enter to instantly retrieve the correct code.
- 4. Drag to Fill: Use the fill handle to drag the formula down and automatically categorize all your search values.
Frequently Asked Questions
Why does my INDEX and MATCH formula return a #VALUE! error?
This usually happens if the formula was not entered as an array in older spreadsheet versions. Try editing the cell and pressing Ctrl+Shift+Enter. Additionally, ensure that all the ranges (e.g., A2:A5 and B2:B5) are exactly the same size.
What if my search value doesn't fall into any of the specified ranges?
If the search value is outside all defined lower and upper limits, the MATCH function will not find a '1' and will return an #N/A error. You can wrap your entire formula in an IFERROR function, like =IFERROR(INDEX(...), "Not Found"), to handle this gracefully.
Can I use XLOOKUP for checking between two values?
Yes. If you have a newer version of Excel that supports XLOOKUP, you can use multiple criteria by multiplying the logical tests in the lookup_array argument: =XLOOKUP(1, (E2>=$A$2:$A$5)*(E2<=$B$2:$B$5), $C$2:$C$5, "Not Found").
