If you want to find a string in a column and return the row number as a result, then this article will be helpful for you. The focus of this article is to explain how to find string in column and return row number in Excel.

**Table of Contents**hide

## Find String in Column and Return Row Number in Excel: 7 Quick Ways

Here, I have taken the following dataset to explain this article. This dataset contains **Sales Person** and** Sales**. Here, I will find a name from the column named **Sales Person **and return the **row number** of that **string**.

### 1. Using MATCH Function to Find String in Column and Return Row Number in Excel

In this method, I will use **the MATCH function** to **find string in column and return row number **in Excel. Here, I will find the string that is in cell **E5 **from the column named **Sales Person** and return the **row number** in cell **F5**.

Let’s see the steps.

**Steps:**

- Firstly, select the cell where you want your
**row number**. Here, I selected cell**F5**. - Secondly, in cell
**F5**write the following formula.

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

Here, in the **MATCH** function, I selected **E5** as **lookup_value**, **B:B** as **lookup_array**, and **0** as **match_type**. The function will return the **row number** of the matched string from column** B**.

- Thirdly, press
**ENTER**to get the result.

**Read More:** **Excel VBA: Return Row Number of Value**

### 2. Applying IFERROR and MATCH Functions to Return Row Number in Excel

In this second method, I will use **the IFERROR function** and the **MATCH** function to** find string in column and return row number**. Here, I will find the strings in cells **E5** and **E6** from the column named **Sales Person** and return their **row numbers** in cells **F5** and** F6**.

Let’s see how it is done.

**Steps:**

- Firstly, select the cell where you want to return the
**row number**. Here, I selected cell**F5**. - After that, in cell
**F5**write the following formula.

`=IFERROR(INDEX(ROW($B$5:$B$9),MATCH(E5,$B$5:$B$9,0)),"No Match")`

**Formula Breakdown**

**MATCH(E5,$B$5:$B$9,0) —->**Here, the**MATCH**function will match the value in cell**E5**with cell range**B5:B9**for the exact match and return the serial number of the found match.**Output: 4**

**ROW($B$5:$B$9) —->**Here, the**ROW**function will return the row numbers of cell range**B5:B9**.**Output: {5;6;7;8;9}**

**INDEX(ROW($B$5:$B$9),MATCH(E5,$B$5:$B$9,0))****—->**turns into**INDEX({5;6;7;8;9},4) —->**Here, the**INDEX**function will return the row number that is in serial**4**.**Output: {8}**

**IFERROR(INDEX(ROW($B$5:$B$9),MATCH(E5,$B$5:$B$9,0)),”No Match”)****—->**turns into**IFERROR(8,”No Match”) —->**Here, the**IFERROR**function will check for errors, and it will return**“No Match”**if any error is found.**Output: {8}**

- Finally, press
**ENTER**and you will get your**row number**.

- After that, drag the
**Fill Handle**to copy the formula.

Now, you can see I have copied the formula and it is working properly.

**Read More: Excel VBA: Find String in Column and Return Row Number**

### 3. Employing Array Formula to Find String in Column and Return Row Number in Excel

Here, I will employ an **Array formula** to find the string from cell **E5** in column and return **row number** in Excel.

- Firstly, select the cell where you want to show your
**row numbers**. Here, I selected cell**F5**. - Secondly, in cell
**F5**write the following formula.

`=MAX((B5:B9=E5)*ROW(B5:B9))`

**Formula Breakdown**

**B5:B9=E5 —->**Here, the formula will check if the value in cell**E5**matches the value in cell range**B5:B9**.**Output: {FALSE;FALSE;TRUE;FALSE;FALSE}**

**ROW(B5:B9) —->**Here, the**ROW**function will return the row numbers of cell range**B5:B9**.**Output: {5;6;7;8;9}**

**(B5:B9=E5)*ROW(B5:B9) —->**turns into**{FALSE;FALSE;TRUE;FALSE;FALSE}*{5;6;7;8;9}****—->**Here, the formula will multiply the values.**Output: {0;0;7;0;0}**

**MAX((B5:B9=E5)*ROW(B5:B9)) —->**turns into**MAX({0;0;7;0;0}) —->**Here, the**MAX**formula will return the highest value found.**Output: 7**

- Thirdly, press
**CTRL+SHIFT+ENTER**to get the result as it is an**array**formula. But, If you are using**Office 365**or any updated version of**Microsoft Excel**then you can press**ENTER**to get the result.

**Read More:** **How to Find Row Number Using VBA in Excel**

### 4. Using MATCH and ROW Functions to Find String in Column

In this method, I will explain how to **find string in a column and return row number **in Excel using the **MATCH** function and **the ROW function**. Here, I will find the string that is in cell **E5** from the column named **Sales Person** and return the** row number** in cell **F5**.

Let’s see the steps.

**Steps:**

- To begin with, select the cell where you want to return the
**row number**. Here, I selected cell**F5**. - Next, in cell
**F5**write the following formula.

`=MATCH(E5,B5:B9,0)+ROW(B5:B9)-1 `

**Formula Breakdown**

**MATCH(E5,B5:B9,0) —->**Here, the**MATCH**function will match the value in cell**E5**with cell range**B5:B9**for an exact match and return the serial number of the found match.**Output: 5**

**ROW(B5:B9) —->**Here, the**ROW**function will return the**row numbers**of cell range**B5:B9**.- Output: {5;6;7;8;9}

**MATCH(E5,B5:B9,0)+ROW(B5:B9)-1 —->**turns into**5+5-1 —->**here, the formula will sum the first**two numbers**and then subtract**1**from the result.**Output: 9**

- Finally, press
**ENTER**to get the result.

**Read More: How to Get Row Number from Range with Excel VBA**

**Similar Readings**

### 5. Applying INDEX, MATCH & ROW Functions in Excel

Here, I will show you how to **find string in column and return row number** in Excel by applying **the INDEX function**, the **MATCH** function and the **ROW** function. I will find the string in **E5** from the column named **Sales Person** and then return the row number in cell **F5**.

Let me explain the steps.

**Steps:**

- Firstly, select the cell where you want to show the row number. Here, I selected cell
**F5**. - Secondly, in cell
**F5**write the following formula.

`=ROW(INDEX(B4:B9,MATCH(E5,B4:B9,0)))`

**Formula Breakdown**

**MATCH(E5,B4:B9,0) —->**Here, the**MATCH**function will match the value in cell**E5**with cell range**B5:B9**for the exact match and return the serial number of the found match.**Output: 2**

**INDEX(B4:B9,MATCH(E5,B4:B9,0)) —->**turns into**INDEX(B4:B9,2) —->**Here, the**INDEX**function will return the value in serial**2**in cell range**B4:B9**.**Output: “Jane”**

**ROW(INDEX(B4:B9,2)) —->**turns into**ROW(“Jane”) —->**Here, the**ROW**function will return the**row number**of the reference.**Output: {5}**

- Thirdly, press
**ENTER**to get the**row number**.

### 6. Use of TEXTJOIN Function to Find String in Column and Return Row Number in Excel

In this method, I will explain how you can use **the TEXTJOIN function** to find **multiple strings** and return their **row numbers** in Excel.

For this example, I have taken the following dataset. This dataset contains names of **Sales Person** and their** State**. There are more than one **Sales Person** from the same **State**. Here, I will find the string in **E5** from the column named **State** and then return their **row numbers**.

Let’s see the steps.

**Steps:**

- Firstly, select the cell where you want your result. Here, I selected cell
**F5**. - Secondly, in cell
**F5**write the following formula.

`=TEXTJOIN(",",,IF(C4:C9=E5,ROW(C4:C9),""))`

**Formula Breakdown**

**IF(C4:C9=E5,ROW(C4:C9),””) —->**Here,**the IF function**will check if the value in**E5**matches the value in cell range**C4:C9**. If it is true then the function will return the**row number**otherwise it will return blank.**Output: {“”;5;””;7;8;””}**

**TEXTJOIN(“,”,,IF(C4:C9=E5,ROW(C4:C9),””)) —->**turns into**TEXTJOIN(“,”,,{“”;5;””;7;8;””},””)) —->**Here, the**TEXTJOIN**function will combine these values from the string.**Output: “5,7,8”**

- Thirdly, press
**ENTER**if you are using**Office 365**or any updated version of**Microsoft Excel**. Otherwise, press**CTRL+SHIFT+ENTER**.

### 7. Employing VBA to Find String in Column and Return Row Number in Excel

Here, I will explain how you can use **VBA** to **find string in column and return row** number in Excel. Here, I will find the string in cell **E5** from column** B** and then show the **row number** in a **message box**.

Let’s see the steps.

**Steps:**

- Firstly, go to the
**Developer**tab. - Secondly, select
**Visual Basic**.

Now, the **Visual Basic** window will open.

- Firstly, select
**Insert**. - Secondly, select
**Module**.

Here, a **Module** will open.

- Now, in that
**Module**write the following code.

```
Sub find_string()
Dim my_WS As Worksheet
Dim string_match As Long
Dim row_number As Long
Dim string_searched As String
Set my_WS = Worksheets("Employing VBA")
string_searched = my_WS.Range("E5").Value
For row_number = 1 To 100
If StrComp(my_WS.Range("B" & row_number).Value, string_searched, vbTextCompare) = 0 Then
string_match = row_number
Exit For
End If
Next row_number
MsgBox "Row Number: " & string_match
End Sub
```

**Code Breakdown**

- Here, I created a
**Sub Procedure**named**find_string()**. - After that, I declared a variable named
**my_WS**as**Worksheet**, a variable named**string_match**as**Long**, a variable named**row_number**as**Long**, and another variable named**string_searched**as**String**. - Then, I used the
**Set statement**to assign the worksheet named**“Employing VBA”**as**my_WS**. - Next, I set the value in cell
**E5**as**string_searched**. - After that, I used a
**For Next loop**to look through the whole column**B**. - Then, I used an
**IF statement**to check if the Value matches any value in column**B**. Here, in the**IF**statement, I used the**StrComp function**to return the value after comparing two strings. - After that, I ended the
**IF**function. - Next, I used the
**MsgBox function**to show the**Row Number**in a message box. - Finally, I ended the
**Sub procedure**.

Now, **save** the code and go back to your worksheet.

- After that, go to the
**Developer**tab. - Next, select
**Macros**.

- Then, select the
**Sub Procedure**you created. - Finally, select
**Run**.

Here, a **MsgBox** with the **row number** of the found string will appear.

- After that, select
**OK**to remove the**MsgBox**.

## Things to Remember

- If you are not using
**Microsoft 365**or later versions then you must press**CTRL+SHIFT+ENTER**for an**Array**formula to get the result. Otherwise, the formula won’t work.

## Practice Section

Here, I have provided a practice sheet for you to practice.

**Download Practice Workbook**

## Conclusion

In this article, I tried to cover how to find string in column and return row number in Excel. Here, I explained 7 quick ways of doing it. I hope this article was helpful for you. Lastly, if you have any questions let me know in the comment section below.