Table of Contents
To assign a value in Excel if a cell contains a certain word, you can use the IF function. This function takes the format IF(logical_test, value_if_true, value_if_false) and tests if the cell contains the word. If it does contain the word, the value_if_true will be returned, otherwise the value_if_false will be returned. You can then use this value for further calculations.
You can use the following formula in Excel to assign a value to a cell if it contains a particular word:
=IF(COUNTIF(B2,"*"&$B$14&"*"), B2, "No")
In this example, if cell B2 contains the word in cell B14, then the value in cell B2 is returned.
Otherwise, a value of “No” is returned instead.
The following example shows how to use this formula in practice.
Example: If Cell Contains Word then Assign Value in Excel
Suppose we have the following dataset in Excel that shows the position of various basketball players:
Suppose we would like to return the name of the position in column C if the corresponding cell in column B contains the word “Starting” (as specified in cell B14) or “No” otherwise.
We can type the following formula into cell C2 to do so:
=IF(COUNTIF(B2,"*"&$B$14&"*"), B2, "No")
We can then click and drag this formula down to each remaining cell in column C:
Notice that column C returns the name of the position in column B if the position contains “Starting” somewhere in the name or it returns “No” otherwise.
How This Formula Works
Recall the formula that we used to assign a value to a cell if it contained a particular word:
=IF(COUNTIF(B2,"*"&$B$14&"*"), B2, "No")
The COUNTIF(B2, “*”&$B$14&”*”) formula checks if cell B2 contains the string “Starting” anywhere in the cell and returns 1 if it does or 0 otherwise.
We then use an IF function to return the value in cell B2 if the COUNTIF function returns 1.
Otherwise, the IF function returns “No” if the COUNTIF function returns 0.