# How to Assign a Value If a Cell Contains a Word in Excel – 4 Methods

This is the sample dataset.

### Method 1 – Combining the IF and the COUNTIF Functions

Steps:

• Prepare a dataset.
• To find cells containing a specific word mentioned in the Criteria header in C4:

• Enter the following formula in C7.

Formula Breakdown

• IF(COUNTIF(\$B7,”*”&\$C\$4&”*”), B7, “Not Found”): the asterisk sign (*) is a wildcard character. It searches for “Chips” substring within B7.
• The COUNTIF function returns one for every substring match.
• The value of the IF function is one (1)=TRUE, it returns the first argument: the desired output.
• Press Enter.
• Drag down the Fill Handle to see the result in the rest of the cells.

### Method 2 – Merging the IF, ISNUMBER, and SEARCH Functions

Steps:

• Set the input criteria in C4.
• Enter the formula in C7.

Formula Breakdown

• IF(ISNUMBER(SEARCH(\$C\$4,\$B7)),B7,”Not Found”): the SEARCH function searches the value of the input criteria in B7. For “Chips”, it returns 11, which is the starting position of the substring.
• The ISNUMBER function converts 11 into TRUE.
• As the IF function’s value is TRUE, it returns the first argument: the desired output.
• Press Enter to see the result.
• Drag down the Fill Handle to see the result in the rest of the cells.

### Method 3 – Combine the  IF, ISNUMBER and FIND Function

Steps:

• Set the input criteria in B7.
• Enter the formula in C7.

Formula Breakdown

• IF(ISNUMBER(FIND(\$C\$4,\$B7)), B7, “Not Found”): The FIND function searches the value of the input criteria in B7 and returns the location. For “Chips” it returns 11, which is the starting position of the substring.
• The ISNUMBER function converts 11 into TRUE.
• As the IF function’s value is TRUE, it returns the first argument: the desired output.
• Press Enter to see the result.
• Drag down the Fill Handle to see the result in the rest of the cells.

### Method 4 – Combine the VLOOKUP Function with the IF and IFERROR Functions

Steps:

• Enter the formula in C7.

Formula Breakdown

• IFERROR(IF(VLOOKUP(\$C\$4,\$B7,1,TRUE)=”Chips”, B7, B7),”Not Found”): , the VLOOKUP function looks up the criteria Chips in B7 and returns the cell’s value, which is Chips.
• the IF function returns the value of the cell: Chips.
• As the IFERROR function’s first argument is not an error, it returns the value: the desired output.
• Press Enter to see the result.
• Drag down the Fill Handle to see the result in the rest of the cells.

<< Go Back to Text | If Cell Contains | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Chinmoy Mondol

Chinmoy Mondol is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find him immersed... Read Full Bio

1 Comment
1. Thank you so much on the Using the COUNTIF function! Can’t get easier to me.

Advanced Excel Exercises with Solutions PDF