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.

**Table of Contents**hide

## 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.

### 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**.

**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**

**How to Sum If Cell Contains Specific Text in Excel (6 Ways)****Find text in an Excel range & return cell reference (3 ways)****Excel Formula If Cell Contains Text Then Return Value in Another Cell**

### 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")`

**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.

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