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.

**Table of Contents**hide

## 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:

`=ROW(C6)`

- 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:

`=MATCH(E5,C:C,0)`

- 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:

`=MATCH(E5,C5:C10,0)+ROW(C5:C10)-1`

- 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:

`=ROW(INDEX(B4:B10,MATCH(E5,C4:C10,0)))`

- 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:

`=MATCH(SMALL(C5:C10,1),C5:C10)`

- 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:

`=TEXTJOIN(",",,IF(C5:C10=E5,ROW(C5:C10),""))`

- 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**