Mashhura Jahan
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.
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}
- INDEX({5;6;7;8;9},4) —-> Here, the INDEX function will return the row number that is in serial 4.
- 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}
- IFERROR(8,”No Match”) —-> Here, the IFERROR function will check for errors, and it will return “No Match” if any error is found.
- 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}
- {FALSE;FALSE;TRUE;FALSE;FALSE}*{5;6;7;8;9} —-> Here, the formula will multiply the values.
- 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
- MAX({0;0;7;0;0}) —-> Here, the MAX formula will return the highest value found.
- 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
- 5+5-1 —-> here, the formula will sum the first two numbers and then subtract 1 from the result.
- 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”
- INDEX(B4:B9,2) —-> Here, the INDEX function will return the value in serial 2 in cell range B4:B9.
- ROW(INDEX(B4:B9,2)) —-> turns into
- ROW(“Jane”) —-> Here, the ROW function will return the row number of the reference.
- Output: {5}
- ROW(“Jane”) —-> Here, the ROW function will return the row number of the reference.
- 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”
- TEXTJOIN(“,”,,{“”;5;””;7;8;””},””)) —-> Here, the TEXTJOIN function will combine these values from the string.
- 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.
