Find the Excel Row Containing a Value Between Two Limits
Question details
Find the worksheet row number where a lookup value falls between minimum and maximum values in separate columns.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains lower limits in one column and upper limits in another, and the user wants to locate the row containing a given value.
- Observed behavior
- The desired result is the worksheet row number of the first range that includes the lookup value.
Check that the minimum and maximum values are numeric and aligned by row. The example uses minimums in A2:A4, maximums in B2:B4, and the lookup value in D2.
Use MATCH to Find the First Row Within the Limits
Use this formula to return the first worksheet row whose minimum and maximum include the lookup value.
The comparisons produce 1 for rows where the value meets both limits. MATCH finds the first such row; adding 1 converts its position within A2:A4 to a worksheet row number.
Type the value you want to find in cell D2.
Select an empty cell and enter =MATCH(1,(D2>=$A$2:$A$4)*(D2<=$B$2:$B$4),0)+1.
Press Enter in Microsoft 365 or Excel 2021. In older Excel versions, press Ctrl+Shift+Enter to confirm it as an array formula.
Edit A2:A4 and B2:B4 to match your data. If the ranges start on a different row, replace +1 with +ROW($A$2)-1, adjusting $A$2 to the first cell of your minimum range.

Find a Matching Range Row in WPS Spreadsheet
Use the same MATCH formula in WPS Spreadsheet to locate the first row whose limits include your lookup value.
- 1. Open the spreadsheet: Open WPS Spreadsheet and choose Open to select your Excel workbook.
- 2. Check the data: Place minimums in A2:A4, maximums in B2:B4, and the lookup value in D2, or note the corresponding cells in your workbook.
- 3. Enter the formula: Select an empty cell and enter =MATCH(1,(D2>=$A$2:$A$4)*(D2<=$B$2:$B$4),0)+1. Adjust the ranges and row offset for your data.
- 4. Confirm and check the result: Press Enter and check the returned row number. If your WPS version requires array-formula entry, select the formula cell and confirm it with Ctrl+Shift+Enter.

Frequently Asked Questions
Does the formula include values equal to a limit?
Yes. The >= and <= comparisons include values equal to either the minimum or the maximum.
Why does the formula return #N/A?
No row may contain the lookup value within both limits. Check the value in D2 and the numeric limits in A2:B4. You can also wrap the formula in IFNA to display a custom message.
What happens if more than one range contains the value?
MATCH returns the first matching position, so the formula returns the worksheet row of the first matching range.
How do I use the formula when my data starts on another row?
Change both ranges to start and end on the rows containing your data. Replace +1 with +ROW(first minimum cell)-1; for example, use +ROW($A$5)-1 if the minimum range starts at A5.




