Find String in Column and Return Row Number in Excel (7 Ways)

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.


Download Practice Workbook


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

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.

Excel Find String in Column and Return Row Number


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.

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

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: How to Use Excel Formula to Find Last Row Number with Data (2 Ways)


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.

Applying IFERROR  and MATCH Functions to Find String in Column and Return Row Number in Excel

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. 

Dragging Fill Handle to Find String in Column and Return Row Number in Excel

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

Read More: Excel VBA: Return Row Number of Value (5 Suitable Methods)


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.

Employing Array Formula to Find String 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 Macros)


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.

Using MATCH and ROW Functions to Find String in Column and Return Row Number in Excel

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 Return Row Number of a Cell Match in Excel (7 Methods)


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.

Applying INDEX, MATCH & ROW Functions to Find String in Column and Return Row Number in Excel

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.

Read More: How to Increment Row Number in Excel Formula (6 Handy Ways)


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.

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

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.

Read More: How to Get Row Number from Range with Excel VBA (9 Examples)


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.

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

Let’s see the steps.

Steps:

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

Opening Visual Basic window in Excel to Find String and Return Row Number

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

VBA Code to Find String in Column and Return Row Number in Excel

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.

Running Macros to Find String in Column and Return Row Number in Excel

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.


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.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo