If you want to assign 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 for doing so. In this article, I have addressed this issue and discussed four different formulas to perform this operation so that you can choose the perfect one for your situation.

## Download the Workbook

You can download the workbook that I used in this article from below and practice with it by yourself.

I attempted to present a real-life example in this dataset. Two types of beverages are represented here, **Chips **and **Cold Drinks**. In a single column named **All Products**, the name and category of the beverages are linked together. Based on the input criteria, values will be assigned to **column C, D, and E**.

## Breakdown of Functions Used in This Article

The formulas that I used here uses the following functions:

**1. The COUNTIFS function:**

This function counts cells that match multiple criteria. The syntax of the **COUNTIFS **function is as follows.

**=COUNTIFS (range1, criteria1, [range2], [criteria2], â€¦)****range1** â€“ The first range to evaluate.

**criteria1** â€“ The criteria to use on **range1**.

**range2 [optional]:** The second range to evaluate.

**criteria2** **[optional]: **The criteria to use on **range2**. Up to 127 **range/criteria pairs** are allowed.

You can learn about this function in detail by reading this documentation from Microsoft.

**2. The SEARCH function:**

This function gets the location of text in a string. The syntax of the **SEARCH **function is as follows.

**=SEARCH (find_text, within_text, [start_num])****find_text**: The text to find.

**within_text:** The text to search within.

**start_num** **[optional]:** Starting position in the text to search. Optional, defaults to 1.

You can learn about this function in detail by reading this documentation from Microsoft.

**3. The FIND function:**

This function gets the location of text in a string. The syntax of the **FIND **function is as follows.

**=FIND (find_text, within_text, [start_num])****find_text: **The text to find.

**within_text: **The text to search within.

**start_num [optional]:** The starting position in the text to search. Optional, defaults to 1.

You can learn about this function in detail by reading this documentation from Microsoft.

**4. The VLOOKUP function:**

This function looks up a value in a table by matching on the first column. The syntax of the **VLOOKUP **function is as follows.

**=VLOOKUP (value, table, col_index, [range_lookup])****value: **The value to look for in the first column of a table.

**table:** The table from which to retrieve a value.

**col_index:** The column in the table from which to retrieve a value.

**range_lookup [optional]:** TRUE = approximate match (default). FALSE = exact match.

You can learn about this function in detail by reading this documentation from Microsoft.

**5. The ISNUMBER function:**

This function tests for numeric values. The syntax of the **ISNUMBER **function is as follows.

**value: **The value to check.

You can learn about this function in detail by reading this documentation from Microsoft.

**6. The IFERROR function:**

This function traps and handles errors. The syntax of the **IFERROR **function is as follows.

**=IFERROR (value, value_if_error)****value:** The value, reference, or formula to check for an error.

**value_if_error: **The value to return if an error is found.

You can learn about this function in detail by reading this **documentation** from Microsoft.

## 4 Formulas to Assign Value If Cells Contain Word

### 1. Using the COUNTIF function

Here, I have checked the **Input Criteria** in the **All Products** column and populate column C, D, and E according to it.

In column **C**, I have returned the cell values of the **All products** column that matched the **Chips** criteria.

The formula is as follows:

`=IF(OR(COUNTIF($B9,"*"&$C$6&"*")), B9, "Not Found")`

*Breakdown:*

*IF(OR(COUNTIF($B9,â€ť*â€ť&$C$6&â€ť*â€ť)), B9, â€śNot Foundâ€ť)*

*=> IF(OR(COUNTIF($B9,"*Chips*")), B9, "Not Found")*

*[Here, the Asterisk sign (*) is a wildcard character. It searched for â€śChipsâ€ť substring within cell B9 which is the â€śRuffles â€“ Chipsâ€ť string.]*

*=> IF(1, B9, "Not Found")*

*[The COUNTIF function returned one for every substring match. As â€śChipsâ€ť is found in cell B9, it returns 1.]*

*=> Ruffles â€“ Chips*

*[As the IF functionâ€™s value is one (1)=TRUE, it returns the first argument which is the desired output.]*

In column **D**, I have returned **TRUE** or **FALSE **based on the **Chips** criteria

The same previous formula is used except here, the **IF** functions output is changed.

`=IF(COUNTIF($B9,"*"&$C$6&"*"), `

`TRUE`

`, `

`FALSE`

`)`

In column **E**, I have returned **1** or **0 **based on the **Chips** criteria

The same previous formula is used except here, the **IF** functions output is changed.

`=IF(COUNTIF($B9,"*"&$C$6&"*"), `

`1`

`, `

`0`

`)`

I have also calculated the total count of the cells that matched the criteria and showed it to **cell C18**.

The formula is as follows:

`=COUNTIF(B9:B15,"*"&$C$6&"*")`

*Breakdown:*

*COUNTIF(B9:B15,â€ť*â€ť&$C$6&â€ť*â€ť)*

*=> COUNTIF(B9:B15,"*Chips*")*

*[Here, the Asterisk sign (*) is a wildcard character. It searched for â€śChipsâ€ť substring within cell range B9:B15.]*

*=> 4*

*[The COUNTIF function returned one for every substring match. As there are four cells in the B9:B15 cell range that contain the â€śChipsâ€ť criteria, it returns 1.]*

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

### 2. Using the SEARCH function

Here, I have checked the **Input Criteria** in the **All Products** column and populate column C, D, and E according to it.

In column **C**, I have returned the cell values of the **All products** column that matched the **Chips** criteria.

The formula is as follows:

**=IF(ISNUMBER(SEARCH($C$6,$B9)),B9,â€ťNot Foundâ€ť)***Breakdown:*

*IF(ISNUMBER(SEARCH($C$6,$B9)),B9,â€ťNot Foundâ€ť)*

*=> =IF(ISNUMBER(11),B9,"Not Found")*

*[The SEARCH function searched the value of the Input Criteria in Cell B7. For â€śChipsâ€ť it returned 11 which is the starting position of the substring.]*

*=> IF(TRUE,B9,"Not Found")*

*[The ISNUMBER function converted 11 into TRUE value.]*

*=> Ruffles â€“ Chips*

*[As the IF functionâ€™s value is TRUE, it returns the first argument which is the desired output.]*

In column **D**, I have returned **TRUE** or **FALSE **based on the **Chips** criteria.

The same previous formula is used except here, the **IF** functions output is changed.

`=IF(ISNUMBER(SEARCH($C$6,$B9)),`

`TRUE`

`,`

`FALSE`

`)`

In column **E**, I have returned **1** or **0 **based on the **Chips** criteria.

The same previous formula is used except here, the **IF** functions output is changed.

`=IF(ISNUMBER(SEARCH($C$6,$B9)),`

`1`

`,`

`0`

`)`

I have also calculated the total count of the cells that matched the **Chips** criteria. You can check out the first method to get the full explanation.

**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. Using the FIND function

This formula is similar to the **SEARCH** function formula except it uses the **FIND **function at its core instead of the **SEARCH **function.

The formula is as follows:

`=IF(ISNUMBER(FIND($C$6,$B9)), B9, "Not Found")`

*Breakdown:*

*IF(ISNUMBER(SEARCH($C$6,$B9)),B9,â€ťNot Foundâ€ť)*

*=> =IF(ISNUMBER(11),B9,"Not Found")*

*[The FIND function searched the value of the Input Criteria in Cell B7 and returned the location. For â€śChipsâ€ť it returned 11 which is the starting position of the substring.]*

*=> IF(TRUE,B9,"Not Found")*

*[The ISNUMBER function converted 11 into TRUE value.]*

*=> Ruffles â€“ Chips*

*[As the IF functionâ€™s value is TRUE, it returns the first argument which is the desired output.]*

You change the outputs by just changing the arguments of the **IF** function in the formula.

`=IF(ISNUMBER(FIND($C$6,$B9)), `

`TRUE`

`, `

`FALSE`

`)`

`=IF(ISNUMBER(FIND($C$6,$B9)), `

`1,0`

`)`

I have also calculated the total count of the cells that matched the **Chips** criteria. You can check out the first method to get the full explanation.

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

### 4. Using the VLOOKUP function

This formula also works like the rest of the methods but with some extra features under its sleeves. Here, I have configured the **VALOOKUP** function for **Approximate Match (True)** which will return the value even if it is not an exact match.

It returned **TRUE** for the **9th row **on but the value that was searched for was **Chips** and a match was found for **Chip**.

The formula is as follows:

*=IFERROR(IF(VLOOKUP($C$6,$B9,1,TRUE)=â€ťChipsâ€ť, B9, B9),â€ťNot Foundâ€ť)**Breakdown:*

`=IFERROR(IF(VLOOKUP($C$6,$B9,1,TRUE)="Chips", B9, B9),"Not Found")`

*=> *`IFERROR(IF("Chip")="Chips", B9, B9),"Not Found")`

[The VLOOKUP function looked up the criteria Chips in range cell B9 and returned the cells value which is Chip.]

=> `IFERROR("Chip","Not Found")`

[The IF function here always returns the value of the cell whether it is TRUE or FALSE. In this case, it returned Chip.]

=> Chip

[As the IFERROR functions first argument is not an error, it returned the value which is the desired output.]

You change the outputs by changing the arguments of the **IF **and **IRERROR** functions in the formula.

`=IFERROR(IF(VLOOKUP($C$6,$B9,1,TRUE)="Chips", `

`TRUE`

`, `

`TRUE`

`),`

`FALSE`

`)`

`=IFERROR(IF(VLOOKUP($C$6,$B9,1,TRUE)="Chips", `

`1`

`, `

`1`

`),`

`0`

`)`

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

## Conclusion

In this article, Iâ€™ve narrowed down four different formulas to assign values in MS Excel if cells contain specific words. I hope you were able to find a solution to your problem. Please leave a comment if you have any suggestions or questions. Thank you.

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