You can use the following formula to find specific text in a range in Excel and return the cell reference:
=CELL("address",INDEX(A2:A11,MATCH("Warriors",A2:A11,0)))
This particular formula will look for the text “Warriors” in the range A2:A11 and return the cell reference where this text is located.
For example, this formula could return $A$6 if this is the cell where the text “Warriors” is found.
The following example shows how to use this formula in practice.
Example: Find Text in Range and Return Cell Reference in Excel
Suppose we have the following list of basketball team names in Excel:
Suppose we would like to find the address of the cell where the team “Warriors” is located.
We can type the following formula into cell B2:
=CELL("address",INDEX(A2:A11,MATCH("Warriors",A2:A11,0)))
The following screenshot shows how to use this formula in practice:
The formula tells us that the text “Warriors” is located in cell $A$6.
Note that if we only wanted to know which position in the list contained “Warriors” then we could use the MATCH function by itself:
=MATCH("Warriors",A2:A11,0)
The following screenshot shows how to use this formula in practice:
The formula tells us that the text “Warriors” is located in the fifth position of the list of team names.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Use COUNTIF with Multiple Ranges
Excel: How to Use an IF Function with Range of Values
Excel: A Simple Formula for “If Not Empty”
