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.

Dataset to get row number from cell value in Excel

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.

Select cell to get row number from cell value in Excel

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

=ROW(D7)

Here,

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

Row function to get row number from cell value in Excel

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.

=MATCH(D7,D:D,0)

Here,

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

MATCH function to get the row number from cell value in Excel

  • 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.

=MATCH(D5,D5:D10,0)+ROW(D5:D10)-1

Use MATCH and ROW function to get Row number from cell value in Excel

  • 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


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

=ROW(INDEX(C5:C10,MATCH(D7,D5:D10,0)))

Here,

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

ROW, INDEX, MATCH functions to get Row number from cell value in Excel

Formula Breakdown

MATCH(D7,D5:D10,0) looks up the value of D7 in the D5:D10 range.

INDEX(C5:C10,MATCH(D7,D5:D10,0)) returns the matched value within the range C5:C10.

Finally ROW(INDEX(C5:C10,MATCH(D7,D5:D10,0))) gives the row number.

  • 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.

Dataset for VBA Code to get row number from cell value in Excel

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

VBA Code to get Row number from cell value in Excel

  • 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.

SMALL & MATCH functions for finding Row number from Cell value in Excel

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.

=MATCH(SMALL(E5:E10,1),E5:E10)

Here,

  • E5:E10= lookup array

Formula Breakdown

SMALL(E5:E10,1) gives the smallest number in the mentioned range (E5:E10).

MATCH(SMALL(E5:E10,1),E5:E10) returns the row number of the previously returned smallest number.

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.

=TEXTJOIN(",",,IF(C5:C10=C7,ROW(C5:C10),""))

Here,

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

Formula Breakdown

Firstly IF(C5:C10=C7,ROW(C5:C10),””) looks for which value in the C5:C10 range is equal to the cell value of C7 and then results in the Row number of the found cell.

And TEXTJOIN(“,”,,IF(C5:C10=C7,ROW(C5:C10),””)) joins the Row numbers separated with commas.

Showing Multiple Row number from cell value in Excel

  • 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

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo