Function Problems

Find the Excel Row Containing a Value Between Two Limits

Amos GikundaAmos Gikunda Nov 18, 2026 868 views

Question details

Find the worksheet row number where a lookup value falls between minimum and maximum values in separate columns.

Find the Excel Row Containing a Value Between Two Limits
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the lookup value

Type the value you want to find in cell D2.

2
Enter the formula

Select an empty cell and enter =MATCH(1,(D2>=$A$2:$A$4)*(D2<=$B$2:$B$4),0)+1.

3
Confirm the formula

Press Enter in Microsoft 365 or Excel 2021. In older Excel versions, press Ctrl+Shift+Enter to confirm it as an array formula.

4
Adjust the ranges if needed

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.

Use MATCH to Find the First Row Within the Limits
Matching behavior: Both limits are inclusive. If ranges overlap, the formula returns the first matching row; if no range matches, it returns #N/A.
Find ranges in WPS Spreadsheet

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. 1. Open the spreadsheet: Open WPS Spreadsheet and choose Open to select your Excel workbook.
  2. 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. 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. 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.
Find the first matching row using minimum and maximum columnsOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet cells and formulasWork with spreadsheets in a free office suite
QA img-9

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.