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

Get FREE Advanced Excel Exercises with Solutions!

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.

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


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.

Return Row Number of a Cell Match in Excel with ROW Function

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:
=ROW(C6)

Return Row Number of a Cell Match in Excel with ROW Function

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

Use MATCH Function to Get Row Number in Excel

Just follow the below steps to perform this method.

STEPS:

  • First, select cell F5.
  • Next, insert the following formula in that cell:
=MATCH(E5,C:C,0)

Use MATCH Function to Get Row Number in Excel

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

Combinations of MATCH & ROW Functions to Extract Row Sequence

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:
=MATCH(E5,C5:C10,0)+ROW(C5:C10)-1

Combinations of MATCH & ROW Functions to Extract Row Sequence

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

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

Let’s see the steps to execute this method.

STEPS:

  • In the beginning, select cell F5.
  • Next, insert the following formula in that cell:
=ROW(INDEX(B4:B10,MATCH(E5,C4:C10,0)))

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

  • 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


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.

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

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:
=MATCH(SMALL(C5:C10,1),C5:C10)

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

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

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

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:
=TEXTJOIN(",",,IF(C5:C10=E5,ROW(C5:C10),""))

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

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

Apply VBA Code to Get Row Sequence of a Cell Match

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

Apply VBA Code to Get Row Sequence of a Cell Match

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

Apply VBA Code to Get Row Sequence of a Cell Match

  • 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

Mukesh Dipto
Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo