Function Problems

How to Find the Row Containing a Value in Multiple Ranges in Excel

Elise Williams Oct 10, 2026 871 views

Question details

The user needs an Excel formula to find the exact row number where a specific lookup value falls between defined "range-from" and "range-to" values.

Excel Formula to Find the Row Containing a Value in Multiple Ranges

Product: Excel
Device & OS: not provided
Scenario: Performing data analysis where a value must be checked against multiple intervals (start and end limits) to determine its row position.
Observed behavior: The user wants a direct formula that outputs the matching row number without having to manually drag formulas down a column.

Before you start

Ensure your start range values and end range values are aligned in parallel columns and do not contain overlapping intervals to guarantee an accurate row match.

Solution 1Recommended

Use the MATCH Function with Array Conditions

This array formula evaluates multiple conditions simultaneously to find the exact row where your lookup value falls between a start and end range.

By multiplying two logical conditions (greater than or equal to the start range, and less than or equal to the end range), Excel creates an array of 1s and 0s. The MATCH function then searches for the value 1 to locate the correct row.

Identify your data cells

Assume your lookup value is in cell A1, your range start values are in B2:B5, and your range end values are in C2:C5.

Input the MATCH formula

Select the empty cell where you want the row number to appear and type the formula: =MATCH(1,(A1>=B2:B5)*(A1<=C2:C5),0)

Confirm the array formula

If you are using older versions of Excel, you must press Ctrl+Shift+Enter to evaluate the array formula. If you are using Microsoft 365 or WPS Spreadsheet, simply press Enter.

Use the MATCH Function with Array Conditions

Relative vs. Absolute Row Numbers: The standard MATCH formula returns the relative position within the selected array (e.g., row 1 of the array). To get the actual worksheet row number, append +ROW(B2)-1 to your formula: =MATCH(1,(A1>=B2:B5)*(A1<=C2:C5),0)+ROW(B2)-1.

Efficient Data Analysis with WPS Office

Easily Evaluate Array Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array formulas like MATCH and INDEX. You can effortlessly manage complex data ranges, lookups, and logical tests with full Microsoft Excel compatibility.

1. Open your workbook: Launch WPS Office and open your spreadsheet containing the start and end data ranges.
2. Input the array formula: Select an empty cell and type the MATCH formula comparing your lookup value to the start and end columns.
3. Get instant results: Press Enter to instantly calculate the exact row number using WPS Spreadsheet's modern, lightning-fast calculation engine.

100% compatibility with Excel formulas, functions, and file formats (.xlsx)Native dynamic array support without needing Ctrl+Shift+Enter shortcutsLightweight application that processes large datasets smoothly on all devices

microsoft office alternative - wps office

Frequently Asked Questions

Why does my MATCH formula produce an error or no output?

This usually happens if you did not press Ctrl+Shift+Enter in older versions of Excel to execute it as an array formula. It can also occur if the lookup value does not fall into any of the specified ranges, resulting in an #N/A error.

Can I use this formula to return a value from another column in the same row?

Yes. You can nest this MATCH formula inside an INDEX function. For example, to retrieve a corresponding value from column D, use: =INDEX(D2:D5, MATCH(1,(A1>=B2:B5)*(A1<=C2:C5),0)).

How do I modify this formula if my ranges have open ends (e.g., greater than 100 with no upper limit)?

You can replace empty or infinite upper limits in your data with a very large number (like 9999999) in the end range column, allowing the same MATCH formula structure to properly evaluate the logical conditions.

Excel & Data Problems