How to Find the Absolute and Relative Row of a Value in an Excel Named Range
Question details
The user needs to locate the absolute (worksheet) row and relative (range) row of a target value that may appear across multiple columns in an Excel named range, while properly handling duplicate values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for a specific target value within a multi-column named range to return its row position, whether it is the first match, the nth match, or all duplicate matches.
- Observed behavior
- Requires specific array formulas to calculate the exact worksheet row number and the relative row number relative to the top of the named range.
Ensure your dataset is properly defined as a named range (e.g., 'Data') and identify the cell containing your target search value (e.g., G3 or G5) before applying these formulas.
Use AGGREGATE and MATCH to Find the First Match
This method searches columns from left to right and rows from top to bottom to reliably find the first instance of your target value.
This approach uses the AGGREGATE function to identify the correct column first, then uses the MATCH function within that specific column to pinpoint the row.
Assuming your named range is 'Data' and your target value is in G3, enter the formula `=AGGREGATE(15,6,COLUMN(Data)/(Data=G3),1)` in a helper cell (e.g., I3) to return the column number.
To find the absolute worksheet row, enter `=MATCH(G3,INDEX(Data,,I3-MIN(COLUMN(Data))+1),0)+MIN(ROW(Data))-1` into your result cell.
To find the relative row (its position within the 'Data' range), subtract the starting row of the range from your absolute row result by appending `-MIN(ROW(Data))+1` to your calculation.
Use MIN or SMALL to Find the First or Nth Match
Ideal for scenarios where you need to extract the exact row number of the first occurrence or a specific subsequent occurrence (like the 2nd or 3rd match) of a value.
Use TEXTJOIN to List All Matching Rows
Best when you have multiple duplicate values scattered in your named range and need a comma-separated list of all their row numbers.
Easily Find and Extract Data in WPS Office
WPS Spreadsheet fully supports advanced array functions like AGGREGATE, SMALL, and TEXTJOIN, making complex lookups across multiple columns effortless. Enjoy a seamless spreadsheet experience for free.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your dataset.
- 2. Define your named range: Go to the Formulas tab, select Name Manager, and define your 'Data' range to make formula writing easier.
- 3. Apply the lookup formulas: Enter the AGGREGATE, MATCH, or TEXTJOIN formulas exactly as you would in Excel. WPS Spreadsheet will process the arrays and return the correct row numbers instantly.
Frequently Asked Questions
What is the difference between an absolute and relative row in a named range?
An absolute row refers to the actual row number on the worksheet (e.g., Row 15). A relative row refers to the row's sequential position within the named range itself (e.g., Row 1 of the range, even if the range starts on worksheet Row 15).
Why does the AGGREGATE function use 15 and 6 as arguments?
In the AGGREGATE function, the first argument '15' specifies that it should behave like the SMALL function. The second argument '6' tells the function to ignore error values, which is necessary when dividing arrays to filter for matching values.
How do I handle named ranges that span across multiple worksheets?
The formulas provided are designed for 2D ranges on a single worksheet. For 3D named ranges (spanning multiple sheets), standard row-matching functions do not natively work. You would need to use advanced VBA macros or Power Query to locate values across multiple sheets.
Why is my formula returning a #NAME? error?
This usually happens if you are using the TEXTJOIN function in an older version of Excel (2016 or earlier) that does not support it. Upgrading your software or switching to WPS Office will resolve this issue, as modern versions fully support TEXTJOIN.
