Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Assign Value If Cell Contains Word in Excel (4 Easy Ways)

If you want to assign a value to a cell based on a certain word in another cell, then you’ll need to build a formula because Excel doesn’t provide a simple way of doing so. The following article will help you in this regard. In this article, I will show you how to assign a value if a cell contains a word in Excel.


Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


4 Easy Ways to Assign Value If Cell Contains Word in Excel

In this article, you will see four easy ways to assign a value if a cell contains a word in Excel. To apply these methods, I will need some Excel functions. You will see the use of IF, COUNTIF, ISNUMBER, SEARCH, FIND, VLOOKUP, and IFERROR functions in the following procedures. Aside from applying formulas to show the result, I will also show the breakdown of those formulas in their relevant section.

To illustrate my further procedure, I will use the following sample data sets where I have two types of beverages.

4 Easy Ways to Assign Value If Cell Contains Word in Excel


1. Combine IF and COUNTIF Functions

In the first procedure, I will use the combination of IF and COUNTIF functions to assign a value based on a specific input criterion. Go through the following steps for a better understanding.

Steps:

  • First of all, prepare the data set like the following image.
  • Here, I want to see through the combination formula which cells contain the specific word mentioned under the Input Criteria header in cell C4.

  • Secondly, to do that, insert the following combination formula in cell C7.
=IF(COUNTIF($B7,"*"&$C$4&"*"), B7, "Not Found")

Combine IF and COUNTIF Functions as An Easy Way to Assign Value If Cell Contains Word in Excel

Formula Breakdown

=IF(COUNTIF($B7,”*”&$C$4&”*”), B7, “Not Found”)

  • IF(COUNTIF($B7,”*”&$C$4&”*”), B7, “Not Found”): Firstly, the asterisk sign (*) is a wildcard character. It searches for the “Chips” substring within cell B7 which is the “Ruffles – Chips” string.
  • Secondly, the COUNTIF function returns one for every substring match. As it finds “Chips” in cell B7, it returns 1.
  • Finally, the value of the IF function is one (1)=TRUE, it returns the first argument which is the desired output.
  • Thirdly, press Enter to check if the specific word matches the cell value of B5 or not.
  • Then, to see all the results in the lower cells use AutoFill.

Read More: If Cell Contains Text Then Add Text in Another Cell in Excel


2. Merge IF, ISNUMBER, and SEARCH Functions

In the second method, I will repeat the same action as in the previous one but with a different formula and combination. This time I will combine the IF, ISNUMBER, and SEARCH functions in a single formula to get my job done. Follow the following steps for a better understanding.

Steps:

  • Firstly, like the previous method, set input criteria in cell C4.
  • Then, based on that criteria, type the following combination formula in cell C7 of the primary data set.
=IF(ISNUMBER(SEARCH($C$4,$B7)),B7,"Not Found")

Formula Breakdown

=IF(ISNUMBER(SEARCH($C$4,$B7)),B7,”Not Found”)

  • IF(ISNUMBER(SEARCH($C$4,$B7)),B7,”Not Found”): Firstly, the SEARCH function searches the value of the input criteria in cell B7. For “Chips” it returns11 which is the starting position of the substring.
  • Secondly, the ISNUMBER function converts 11 into a TRUE value.
  • Finally, as the IF function’s value is TRUE, it returns the first argument which is the desired output.
  • Secondly, after pressing Enter, you will get the desired result based on the value of cell B7.
  • Finally, with the help of AutoFill, drag the formula to the lower cells to get those results as well.

Read More: Excel If Cell Contains Text Then Return Value (8 Easy Ways)


Similar Readings


3. Combine IF, ISNUMBER with FIND Function

This time, you will see the use of the FIND function in the combination of IF and ISNUMBER functions as the third procedure. By using this combination, I will check whether a specific input criterion matches the assigned cell values or not. To find the details of this procedure, see the following steps.

Steps:

  • First of all, to check if the word in the input criteria is in the cell value of B7, use the following combination formula in cell C7.
=IF(ISNUMBER(FIND($C$4,$B7)), B7, "Not Found")

Formula Breakdown

=IF(ISNUMBER(FIND($C$4,$B7)), B7, “Not Found”)

  • IF(ISNUMBER(FIND($C$4,$B7)), B7, “Not Found”): Firstly, The FIND function searches the value of the input criteria in cell B7 and returns the location. For “Chips” it returns11 which is the starting position of the substring.
  • Secondly, the ISNUMBER function converts 11 into a TRUE value.
  • Finally, as the IF function’s value is TRUE, it returns the first argument which is the desired output.
  • Secondly, to see the answer after using the formula press Enter.
  • Then, for showing the results for the lower cells of the column drag the Fill Handle.

Read More: How to Return Value If Cells Contain Certain Text from a List


4. Insert VLOOKUP Function into IF and IFERROR Functions

In the last method of this procedure, I will insert the VLOOKUP function in the combination formula of the IFERROR and IF functions. This formula also works like the rest of the methods, but with some extra features under its sleeve. Here, I will use the VLOOKUP function for Approximate Match (True), which will return the value even if it is not an exact match. You will see the detailed procedure in the following steps.

Steps:

  • Firstly, to see the match with the input criteria, insert the following formula that has the IFERROR, IF and VLOOKUP functions for getting the desired output.
=IFERROR(IF(VLOOKUP($C$4,$B7,1,TRUE)="Chips", B7, B7),"Not Found")

Insert VLOOKUP Function into IF and IFERROR Functions as An Easy Way to Assign Value If Cell Contains Word in Excel

Formula Breakdown

=IFERROR(IF(VLOOKUP($C$4,$B7,1,TRUE)=”Chips”, B7, B7),”Not Found”)

  • IFERROR(IF(VLOOKUP($C$4,$B7,1,TRUE)=”Chips”, B7, B7),”Not Found”): Firstly, the VLOOKUP function looks up the criteria Chips in range cell B7 and returns the cell’s value which is Chips.
  • Secondly, the IF function here always returns the value of the cell whether it is TRUE or FALSE. In this case, it returns Chips.
  • Finally, as the IFERROR function’s first argument is not an error, it returns the value which is the desired output.
  • Secondly, to get the result from the above formula hit the Enter
  • Then, to use the formula for the rest of the cells of that column use AutoFill.

Read More: How to Use VLOOKUP If Cell Contains a Word within Text in Excel


Things to Remember

  • Be careful, while giving cell references in the formulas. You will not get a correct result with an incorrect cell reference.
  • Other than the last method, the other three methods will require an exact match to find the input criteria from the cell values.

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you can assign a value if a cell contains a word in Excel. Please share any further queries or recommendations with us in the comments section below.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.


Related Articles

Chinmoy Mondol

Chinmoy Mondol

Greetings! Thank you for visiting my profile. I am Chinmoy Mondol. I am a conscientious, tech enthusiast individual with a voracious appetite for knowledge and a desire to learn more. I graduated from American International University-Bangladesh with a Bachelor's Degree in Computer Science and Engineering. I enjoy using my skills to contribute to the exciting technological advances that happen every day. Constant advancement and personal development are my guiding principles.

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

Leave a reply

ExcelDemy
Logo