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.
How to Assign Value If Cell Contains Word in Excel: 4 Easy Ways
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.
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")
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.
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.
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 returns 11 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, to show the results for the lower cells of the column drag the Fill Handle.
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")
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.
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.
Download Practice Workbook
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.
<< Go Back to Text | If Cell Contains | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Thank you so much on the Using the COUNTIF function! Can’t get easier to me.