When working with a dataset in Excel, you may need to find the row number of a specific value to retrieve related information.
For example, suppose you manage a list of suppliers. In that case, you may need to find the number of the row containing a particular supplier’s name to access their contact details, order history, payment terms, and other relevant data.
I will show you ways of finding the row number of matching value in Excel.
Method #1: Use the XLOOKUP Function
You can use the XLOOKUP function, which is only available in Excel 365, and later, to find the row number of a matching value in Excel.
Suppose you have the dataset below listing some states, their land areas, and capital cities. You want to find the number of the row of the cell in the dataset containing the state name ‘Arizona.’
You can use the formula below to perform the task:
=XLOOKUP(F2, A2:A11, ROW(A2:A11))
The formula returns the value 7 indicating that the seventh row in the worksheet contains the state name ‘Arizona.’
The formula searches for the value in F2 within the range A2:A11. If a match is found, it returns the row number of the matched value from that range.
Also read: Find Last Occurrence of a Value in a Column in Excel
Method #2: Use the XMATCH Function
You can use the XMATCH function to find the row number of matching value in Excel. The XMATCH function finds the relative position of an item in a row or column.
Note: The XMATCH function is only available in Excel 365 and later. You can use the MATCH function if you have an older version of Excel.
Suppose you have the dataset below listing some states, their land areas, and capital cities. You want to find the number of the row of the cell containing the state name ‘Arizona.’
You can use the below formula to do it:
=XMATCH(F2,A2:A11,0)
The formula returns the value 7 indicating that the seventh row in the worksheet with the dataset contains the state name ‘Arizona.’
The formula searches for the exact match of the value in cell F2 in the cell range A1:A11 and returns the row number of the matching value.
Note: The formula will return correct results only if the target range starts at row 1 of the worksheet, as the XMATCH function returns the relative position of an item within the specified row or column.
If you change the state name in cell F2 to, say ‘Wyoming,’ the formula automatically updates the row number in cell F3, as shown below.
Also read: Get the Cell Address Instead Of Value In Excel
Method #3: Use the Combination of XMATCH and ROW Functions
You can use the combination of XMATCH and ROW functions to find the row number of matching value in Excel.
Suppose you have the dataset below listing some states, their land areas, and capital cities. You want to find the number of the row of the cell in the worksheet with the state name ‘Arizona.’
You can use the formula below to perform the task:
=XMATCH(F2,A2:A11,0)+ROW(A2)-1
The formula returns the value 7 indicating that the seventh row of the worksheet containing the target dataset contains the state name ‘Arizona.’
Here’s how the formula works:
- XMATCH(F2, A2:A11, 0) – This part of the formula returns the relative position of the value in cell F2 in the given cell range, which is 6.
- ROW(A2) – This part of the formula returns the row number of cell A2, which is 2.
- + ROW(A2) – 1 – This part of the formula adjusts the relative position returned by XMATCH to reflect the actual row number in the worksheet. The calculation is 6 + 2 -1 = 7. Subtracting 1 corrects for XMATCH returning a relative position starting from 1.
Also read: Find the Closest Match in Excel (Nearest Value)
Method #4: Use the Combination of INDEX, XMATCH and ROW Functions
You can use a formula combining the INDEX, XMATCH, and ROW functions to find the row number of matching value in Excel.
Suppose you have the dataset below listing some states, their land areas, and capital cities. You want to find the number of the row of the cell in the dataset containing the state name ‘Arizona.’
You can use the formula below to perform the task:
=ROW(INDEX(A2:A11,XMATCH(F2,A2:A11,0)))
The formula returns the value 7 indicating that the seventh row of the worksheet containing the target dataset contains the state name ‘Arizona.’
The formula finds the value in F2 within the range A2:A11 and returns the row number of the matching value in the worksheet.
Method #5: Use a Combination of TEXTJOIN and IF Functions
In all the above methods, I have shown you how to find the row number of a single matching value in Excel. What about if you want to find row numbers of multiple matching values? That’s where the combination of TEXJOIN and IF functions comes in.
The TEXTJOIN function joins or concatenates a range or list of text strings using a delimiter. The IF function checks whether a specified condition is met and returns one value if it is met and another if it isn’t.
In Excel, you can use a formula combining the TEXTJOIN and IF functions to find row numbers of multiple matching values.
Suppose you have the dataset below of various tourist attractions in column A and the states where they are located in column B. You want to find the row numbers of the cells with the state name ‘Nevada.’
You can use the formula below to do it:
=TEXTJOIN(",",,IF(B2:B11=E2,ROW(B2:B11),""))
The formula returns the values 3 and 11 indicating that the third and eleventh rows of the worksheet with the target dataset contain the state name ‘Nevada.’
The formula looks for the value in E2 within the range B2:B11 and returns the row numbers where matches occur, combining them into a comma-separated string. If no matches are found, it returns an empty string.
Here’s how the formula works:
- IF(B2:B11=E2,ROW(B2:B11),””) – This part of the formula checks if the value in the cell range B2:B11 equals the value in cell E2. If the condition is TRUE, the formula returns the row number of that cell, otherwise, it returns an empty string.
- TEXTJOIN(“,”,,IF(B2:B11=E2,ROW(B2:B11),””)) – The TEXTJOIN function combines the non-empty results from the IF function into a string, with each value separated by a comma (,). The TEXTJOIN function ignores empty values by default.
Method #6: Use VBA
You can use VBA code when you want a more versatile solution to finding the row number of matching value in Excel.
Suppose you have the dataset below listing some states, their land areas, and capital cities. You want to find the number of the row of the cell containing the state name ‘Arizona.’
You can use the VBA code below to perform the task:
Sub FindMatchingValue()
Dim wb As Workbook
Dim ws As Worksheet
Dim FoundCell As Range
Dim ValueToMatch As String
Dim SearchRange As Range
Dim RangeAddress As String
Set wb = ActiveWorkbook
Set ws = ActiveSheet
ValueToMatch = InputBox("Enter the value to search for:", _
"Find Matching Value")
If ValueToMatch = "" Then
MsgBox "No value entered. Exiting the macro."
Exit Sub
End If
RangeAddress = InputBox("Enter the range to search _
(e.g., A:A or A1:B100):", "Search Range")
On Error Resume Next
Set SearchRange = ws.Range(RangeAddress)
On Error GoTo 0
If SearchRange Is Nothing Then
MsgBox "Invalid range. Exiting the macro."
Exit Sub
End If
Set FoundCell = SearchRange.Find(What:=ValueToMatch)
If Not FoundCell Is Nothing Then
MsgBox ValueToMatch & " found in row: " & FoundCell.Row & ",_ column: " & FoundCell.Column
Else
MsgBox ValueToMatch & " not found in the specified range."
End If
End SubWhen you run the code it first prompts you to enter the value to search for.
In this example, I am searching for the value ‘Arizona.’
The code then prompts you for the range to search.
In this example, I am searching range A2:A11.
Finally, the code displays a message box indicating the row and column number of the matching value.
I have shown you ways to find the row numbers of matching values in Excel. I hope you found the tutorial helpful.
Other Excel articles you may also like:
