How to Return Row Number of a Cell Match in Excel (7 Methods)

In this tutorial, we will demonstrate how to return the row number of a cell match in excel. If we say it more clearly, we will select a value from a dataset and extract the row number of that value. To do this we will use different functions or combinations of different functions throughout this article.

Download Practice Workbook

We can download the practice workbook from here.

7 Methods to Return Row Number of a Cell Match in Excel

In this article, we will discuss 7 methods to return the row number of a cell match in excel. To make you understand the process better we will use the same dataset for all the methods of this article except method number 5. The dataset that we will use consists of the names of different people and their native countries. We will take one value either from the Name column or the Country column. Then we will find out in which row that particular value lies.

1. Return Row Number of a Cell Matching Excel with ROW Function

First and foremost, we will return the row number of a cell match in Excel with the ROW function. The ROW function in excel returns the reference’s row number. In the following dataset, we will extract the row number of the name Chris in cell F5.

Let’s see the steps to perform this action:

STEPS:

• To begin with, select cell F5.
• In addition, write down the =ROW( part in the formula bar of that cell.
• After writing that part, select the cell that contains the name, Chris. So, we get the following formula in the formula bar:

• Then, press Enter.
• In the end, we can see the row number of the name Chris in cell F5.

Read More: Excel VBA: Return Row Number of Value (5 Suitable Methods)

2. Use MATCH Function to Get Row Number in Excel

In this method, we will use the MATCH function to return the row number of matches in excel. The MATCH function searches a range of cells for a specified item and then returns the item’s relative location in the range. In the following dataset, we will figure out in which row the country name Canada lies.

Just follow the below steps to perform this method.

STEPS:

• First, select cell F5.
• Next, insert the following formula in that cell:

• Then, hit Enter.
• Finally, the above commands return the row number of the country name Canada in cell F5.

Read More: Excel Find Matching Values in Two Columns

3. Combinations of MATCH & ROW Functions to Extract Row Sequence

We can also use a combination of the MATCH and ROW functions to return the row number of a cell match. In cell F5 we will input the row number in which the value Canada lies in the Country column.

Let’s take a look at the steps to do this.

STEPS:

• Firstly, select cell F5.
• Secondly, write down the following formula in that cell:

• Then, press Enter.
• Lastly, we get the row number of the value Canada is 6 in our dataset.

Read More: Copy Values to Another Cell If Two Cells Match in Excel: 3 Methods

4. Combine INDEX, MATCH & ROW Functions to Return Row Number of a Match in Excel

A combination of INDEX, MATCH & ROW functions is another way to return the row number of a match in excel.

In Microsoft Excel, the INDEX function in Excel returns the value at a certain point in a range or array.

Again we will find in which row of column C the country name Canada is located. We will return the numeric value of the row number in cell F5.

Let’s see the steps to execute this method.

STEPS:

• In the beginning, select cell F5.
• Next, insert the following formula in that cell:

• Then, hit Enter.
• So, the above actions return the row number of the country name Canada in cell F5.

🔎 How Does the Formula Work?

• MATCH(E5,C4:C10,0): This part searches for the value of cell E5 within the range (C4:C10).
• INDEX(B4:B10,MATCH(E5,C4:C10,0): This part returns the reference of the matched value within the range (B4:B10).
• ROW(INDEX(B4:B10,MATCH(E5,C4:C10,0))): Returns the row number of the INDEX.

Read More: How to Vlookup and Pull the Last Match in Excel (4 Ways)

Similar Readings

• How to Get Row Number from Range with Excel VBA (9 Examples)
• How to Increment Row Number in Excel Formula (6 Handy Ways)
• [Fixed!] Missing Row Numbers and Column Letters in Excel (3 Solutions)
• How to Use Excel Formula to Find Last Row Number with Data (2 Ways)
• How to Use Variable Row Number as Cell Reference in Excel

5. Merge SMALL & MATCH Functions to Get Row Number of Matched Value

We can also use the combination of SMALL & MATCH functions to return the row number of a matched value in excel.

When a list is sorted by value in ascending order, the excel SMALL function returns a numeric value based on its location in the list.

To illustrate this method, we will use a slightly different dataset from the previous ones since the SMALL function only deals with numeric values. In the following dataset, we have country names and their areas. We will find out in which row the lowest value of the area is located. Then we will return that value in cell E5.

Let’s see the steps to perform this method.

STEPS:

• To begin with, select cell E5.
• In addition, insert the following formula in that cell:

• Then, press Enter.
• In the end, we can see that the lowest value of the area in column C is located in row number 3.

🔎 How Does the Formula Work?

• SMALL(C5:C10,1): This part returns the smallest numeric value from the range (C5:C10).
• MATCH(SMALL(C5:C10,1),C5:C10): Returns the row number of the smallest value in cell E5.

NOTE:

Since the MATCH function returns the relative position of a value from a data range, the above process returns the value 3 instead of 7.

6. Return All Row Numbers of a Cell Match in One Cell in Excel

Suppose, we have a dataset in which we have multiple same values in a single column but in different rows. We want to input the row numbers of those values in a single cell. To do this kind of problem we will use a combination of the TEXTJOIN, IF, and ROW functions.

The TEXTJOIN function joins text from various ranges and/or strings, with a delimiter that you define between each text value to be joined.

In the following dataset, we can see that in column C the value of the ‘United States’ is present 3 times.

Let’s see the steps to return the row numbers having the same value in a single cell.

STEPS:

• First, select cell F5.
• Next, Input the following formula in that cell:

• Then, press Enter.
• Finally, in cell F5 we can see the row numbers of the same values from column C.

🔎 How Does the Formula Work?

• IF(C5:C10=E5,ROW(C5:C10),””): In this part the IF formula checks which values in the range (C5:C10) are equal to the value of cell E5. After that, it returns the row number of that cell.
• TEXTJOIN(“,”,,IF(C5:C10=E5,ROW(C5:C10),””)): Combines the row numbers of the previous step with a comma in a single cell F5.

Read More: How to Match Data in Excel from 2 Worksheets

7. Apply VBA Code to Get Row Sequence of a Cell Match

If you are an advanced excel user, you can use a VBA (Visual Basic for Applications) code to return the row number of a cell match in excel. Using VBA code we can do any kind of task in excel more rapidly. In the following dataset, we will insert a VBA code to find out the row number of the value Canada in column C.

Let’s see the steps to apply the VBA code.

STEPS:

• Firstly, right-click on the active sheet named VBA.
• Secondly, select the option ‘View Code’.

• Then, a blank VBA module will appear.
• Thirdly, insert the following code in that blank module:

Sub ReturnRowNumber()
Dim Wsheet As Worksheet
Dim Row_Match As Long
Dim j As Long
Dim Value_Searched As String
Set Wsheet = Worksheets("VBA")
Value_Searched = "Canada"
For j = 1 To 10000
If StrComp(Wsheet.Range("C" & j).Value, Value_Searched, vbTextCompare) = 0 Then
Row_Match = j
Exit For
End If
Next j
MsgBox Row_Match
End Sub

• Input the value Canada for the variable ‘Value_Serched’ in the code. We have highlighted that part in the following image.
• Now, click on the Run button or press the F5 key to run the code.

• Lastly, we get a message box showing that the row number of the value Canada in column C is 6.

Read More: How to Find Row Number Using VBA in Excel (4 Macros)

NOTE:

In the above code if you want to search any data from your dataset you will just have to modify the highlighted parts of the code from the given image above. Instead of VBA use the name of your worksheet. Change the value Canada to another value that you want to search in your worksheet. Instead of column range C, you will input the column range in which you want to search.

Conclusion

In conclusion, this tutorial encapsulates the ideas to return the row number of a cell match in excel. Use the practice worksheet that comes with this article to put your skills to the test. If you have any questions, please leave a comment below. Our team will try to reply to you as quickly as possible. Keep an eye out for more creative Microsoft Excel solutions in the future.

Related Articles

• Excel VBA: Copy Row If Cell Value Matches (2 Methods)
• Sum All Matches with VLOOKUP in Excel (3 Easy Ways)
• How to Find Case Sensitive Match in Excel ( 6 Formulas)
• How to Match Names in Excel Where Spelling Differ (8 Methods)
• Find String in Column and Return Row Number in Excel (7 Ways)
• Excel VBA: Find String in Column and Return Row Number