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

We have a sample dataset that contains Sales Person and Sales information. We will find a name from the column named Sales Person and return the row number of that string.

Method 1 – Using the MATCH Function to Find a String in a Column and Return the Row Number in Excel

We will find the string that is in cell E5 from the column named Sales Person and return the row number in cell F5.

Steps:

Select the cell where you want your row number. We selected cell F5.
Insert the following formula.

=MATCH(E5,B:B,0)

Press Enter to get the result.

Method 2 – Applying IFERROR and MATCH Functions to Return the Row Number in Excel

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

Steps:

Select the cell where you want to return the row number. We selected cell F5.
Insert 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}

Press Enter.

Drag the Fill Handle down to copy the formula.

Here’s the result.

Method 3 – Using an Array Formula to Find a String in a Column and Return the Row Number in Excel

We’ll find the string from cell E5 in column and return row number in Excel.

In cell F5, insert 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

Press Ctrl + Shift + Enter to get the result as it is an array formula. If you are using Office 365 or any updated version of Microsoft Excel, you can press Enter to get the result.

Method 4 – Using MATCH and ROW Functions to Find String in Column

We will find the string that is in cell E5 from the column named Sales Person and return the row number in cell F5.

Steps:

In cell F5, insert 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

Press Enter.

Method 5 – Applying INDEX, MATCH, and ROW Functions in Excel

We will find the string in E5 from the column named Sales Person and then return the row number in cell F5.

Steps:

In cell F5, insert 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}

Hit Enter.

Method 6 – Use the TEXTJOIN Function to Return Multiple Results

We modified the dataset to contain people’s names and their State. There are more than one Sales Person from the same State. We will find the string in E5 from the column named State and then return their row numbers.

Steps:

In cell F5, insert 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”

Press Enter if you are using Office 365 or press Ctrl + Shift + Enter.

Method 7 – Using VBA

We will find the string in cell E5 from column B and then show the row number in a message box.

Steps:

Go to the Developer tab.
Select Visual Basic.

The Visual Basic window will open.

Select Insert.
Select Module.

A Module will open.

Insert 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

We created a Sub Procedure named find_string().
We 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.
We used the Set statement to assign the worksheet named “Employing VBA” as my_WS.
We set the value in cell E5 as string_searched.
We used a For Next loop to look through the whole column B.
We 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.
We used the MsgBox function to show the Row Number in a message box.