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