You can use the INDEX and MATCH functions in Excel to look up a specific value in one column and return a corresponding value in another column.
You can use the following syntax with INDEX and MATCH to look up a cell that contains specific text:
=INDEX(A2:B11,MATCH("*Warr*",A2:A11,0),2)
This particular formula looks up the value in the range A2:A11 that contains the substring “Warr” and returns the corresponding value in column 2 of the range A2:B11.
Note: The asterisks ( * ) are used as wildcard characters. This means any character can come before or after “Warr” in the cell.
The following example shows how to use this syntax in practice.
Example: How to Use INDEX MATCH if Cell Contains Text in Excel
Suppose we have the following dataset in Excel that contains information about points scored by basketball players on various teams:
Suppose we would like to look up the substring “Warr” in the Team column and return the corresponding value in the Points column.
We can type the following formula into cell G4 to do so:
=INDEX(A2:B11,MATCH("*Warr*",A2:A11,0),2)
The following screenshot shows how to use this formula in practice:
The formula returns 18, which is the value in the Points column that corresponds to the team with “Warr” in their name:
Note that you could also use the following formula to search for the entire team name instead of just a substring:
=INDEX(A2:B11,MATCH("Warriors",A2:A11,0),2)
The following screenshot shows how to use this formula in practice:
This formula also returns 18, which is the points value that corresponds to the Warriors.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Use IF Function with INDEX MATCH
Excel: How to Use SUM with INDEX MATCH
Excel: Use INDEX and MATCH to Return Multiple Values Vertically
