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

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

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

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

`)`

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