'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Find the Range Name for a Value Between Two Numbers in Excel
Question details
The user needs to look up a specific value against separate range-start and range-end columns to find its corresponding category or range name.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Categorizing or matching a specific number into predefined numeric tiers that are spread across separate start and end columns.
- Observed behavior
- A standard VLOOKUP fails to return accurate results because the lookup must evaluate two separate conditions (start and end values), especially when the defined ranges are not continuous.
Verify that your numeric ranges do not overlap to prevent multiple conflicting matches, and check your Excel version to see if modern array functions like XLOOKUP are supported.
Use INDEX and MATCH for a Two-Condition Lookup
This method utilizes an array-based INDEX and MATCH combination to check both range-start and range-end columns simultaneously. It works well across most versions of Excel.
By multiplying two logical conditions, Excel creates an array of 1s and 0s. The MATCH function then looks for the number 1, identifying the exact row where both the start and end conditions are true.
Select your columns and use the Name Box (next to the formula bar) to define optional names for your ranges. For example, name A2:A6 as RangeStart, B2:B6 as RangeEnd, and C2:C6 as RangeName.
Select your output cell (e.g., F2) and type the formula: =IFERROR(INDEX(RangeName,MATCH(1,(E2>RangeStart)*(E2<RangeEnd),0)),"Not Found"). Adjust the > and < to >= and <= if the boundary numbers are inclusive.
If you are using an older version of Excel, you must press Ctrl+Shift+Enter instead of just Enter to evaluate this as an array formula.
Use XLOOKUP with Boolean Logic
For users with newer Excel versions, XLOOKUP provides a cleaner, more direct syntax to evaluate multiple criteria without needing Ctrl+Shift+Enter.
Sort the Data Table for Easier Processing
Sorting your table by the starting value can make it easier to manage ranges, making alternative approximate match formulas viable if the ranges are strictly continuous.
Easily Find Values Between Two Numbers in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, INDEX/MATCH combinations, and modern lookup functions, allowing you to seamlessly process complex multi-condition data lookups without hassle.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your Excel workbook containing the numeric ranges.
- 2. Apply the lookup formula: Select your destination cell and input the multi-condition INDEX/MATCH or XLOOKUP formula.
- 3. Press Enter to evaluate: WPS Spreadsheet instantly processes the boolean array logic and retrieves the accurate range name.
Frequently Asked Questions
Why does my XLOOKUP formula return a #NAME? error?
The #NAME? error occurs if your version of Excel does not support the XLOOKUP function. XLOOKUP is only available in Microsoft 365 and Excel 2021 or newer. If you are on an older version, use the INDEX and MATCH array method.
Can I use VLOOKUP to find a value between two numbers?
Yes, but only if your numeric ranges are completely continuous (without gaps) and sorted in ascending order. If these conditions are met, you can use VLOOKUP with the approximate match argument (TRUE or 1) based on just the start column.
How does the boolean logic (E2>=RangeStart)*(E2<=RangeEnd) work?
It evaluates each condition separately, creating two arrays of TRUE (1) and FALSE (0) values. Multiplying them acts like an AND operator; 1*1 equals 1 (both conditions met), while any other combination results in 0. The formula then searches for the number 1 to find the correct row.
