How to Get Row Number from Cell Value in Excel (5 Methods)

If you are facing difficulties in finding the row number of a cell in an excel workbook, then you have landed in the right place. There are several ways available for finding out the row number of a cell value. In this article, I will show you how to get the row number from cell value in Excel. We will apply different Excel built-in functions to serve our purpose.

Download Practice Workbook

You can download the practice book from the link below.

5 Ways to Get Row Number from Cell Value in Excel

In this section, you will find 5 effective and suitable ways to get the row number in Excel. I will demonstrate them one by one here. Let’s check them now!

1. Using ROW Function

Let’s say, we have got a dataset of Names of some customers, their respective ID, and Purchased Products.

From this dataset, we want to get the row number for a random cell value. Here, we will perform this task using the ROW function. In order to do so, proceed with the following steps.

Steps:

• First of all, choose a cell that you want to get the row number that it lies in.

• Then, create a column for showing the row number and write the following formula.

Here,

• D7= The cell whose Row number is to be counted
• Now, hit ENTER and the cell will show the result.

Read More: How to Use Excel Formula to Find Last Row Number with Data (2 Ways)

2. Row Number from Cell Value Using MATCH Function

For the same set of data, we will now apply the MATCH function to get the row number. For this, follow the steps below.

Steps:

• Firstly,  apply the following formula to the selected cell.

Here,

• D7= The cell whose Row number is to be counted

• Then, press ENTER and you will get the row number.

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

3. Combining MATCH and ROW Functions

In this section, we will now combine the MATCH and ROW functions to get the row number. In order to do so, just proceed with the steps below.

Steps:

• Firstly, choose a cell value (i.e. Apple) whose row number is to be found.
• Then, type the following formula.

• Then, press ENTER and you will see that all the row number of the cells down the selected cell has been shown.

Note: You can use this formula when you need to find the row number of all the cells of a list.

Read More: How to Get Row Number of Current Cell in Excel (4 Quick Ways)

Similar Readings

• Excel VBA: Return Row Number of Value (5 Suitable Methods)
• Excel VBA: Find String in Column and Return Row Number
• Find String in Column and Return Row Number in Excel (7 Ways)

4. Use Combination of ROW, INDEX, and MATCH Functions

We will now use the combination of ROW, INDEX, and MATCH functions to find the row number in Excel. So, let’s start the process like the one below.

Steps:

• First of all, type the following formula

Here,

• D7= The cell whose Row number is to be counted

• Then, hit ENTER to get the output.

5. Using VBA to Get Row Number

You can also get the row number of a cell value by using the VBA code.

For the dataset, we want to find the row number for the cell value Banana. Let’s start the procedure.

Steps:

• Firstly, right-click on the sheet name.
• Then, a menu list will appear. Here, choose View Code from the menu list.

• Then, a window will appear. Enter your code here. You can use the following.

Code:

Sub ShowRowNumber()

Dim Wksheet As Worksheet
Dim Row_Matching As Long
Dim i As Long
Dim Value_Searching As String

Set Wksheet = Worksheets("VBA")

Value_Searching = "Banana"
For i = 1 To 10000
If StrComp(Wksheet.Range("C" & i).Value, Value_Searching) = 0 Then
Row_Matching = i
Exit For
End If
Next i
MsgBox Row_Matching
End Sub

• Now, click the Run option or press F5 to run the code.
• Finally, a message box will show you the row number of the selected cell.

Read More: How to Get Row Number from Range with Excel VBA (9 Examples)

Useful Cases of Getting Row Number in Excel

As you’ve known some ways to get the row number from cell value you will eager to use those in some scenarios, for your convenience we’re showing you a couple of cases you may use the row number methodology.

1. Finding Row Number Within a List

Now, we will show the process of finding the Row Number of the smallest value of a list. We will use SMALL and MATCH functions.

Let’s say, for the same customers and the purchased products, we have the price of the products also.

You can see that Banana has the smallest price in the product list and we want to find the row number of that product with the smallest price.

For this, proceed with the steps below.

Steps:

• Firstly, apply the following formula.

Here,

• E5:E10= lookup array

This is how you will find the Serial number of the list that it lies in. Notice that, Banana lies in the 3rd position in the list and so, the result is 3.

2. Show Matching Row Numbers in a Single Cell

We will now show the Row Number of multiple matching cells which are the same in value. Let’s say, like in the previous dataset, Mike, David, and Antony have bought the same product (i.e. Apple).

We want to find the row numbers of these 3 cells. Follow the steps stated below to do so.

Steps:

• Firstly, type the following formula to get the row numbers.

Here,

• C7= The cell whose Row number is to be counted

• Then, press ENTER and the cell will show multiple results (i.e. 5,7,10 as the same values lie in these 3 rows).

Read More: How to Use Variable Row Number as Cell Reference in Excel

Conclusion

In this article, I have tried to show you some methods to get the row number from a cell value in Excel. I hope this article has shed some light on your way to get the row number from cell value in an Excel workbook. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. This will help me enrich my upcoming articles.

Related Articles

• VBA Range with Variable Row Number in Excel (4 Examples)
• [Fixed!] Missing Row Numbers and Column Letters in Excel (3 Solutions)
• How to Increment Row Number in Excel Formula (6 Handy Ways)