If Cell Contain Word Then Assign Value in Excel (4 Formulas)

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.

The dataset

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.

=ISNUMBER (value)

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.

Entering the COUNTIF function formula

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)

Changing the output with desired output

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)

Changing the output with desired output

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

Counting the total matches

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.

Entering the SEARCH function formula

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)

Changing the output with desired output

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)

Changing the output with desired output

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.

Entering the FIND function formula

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)

Changing the output with desired output

=IF(ISNUMBER(FIND($C$6,$B9)), 1,0)

Changing the output with desired output

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.

Entering the VLOOKUP function formula

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)

Changing the output with desired output

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

Changing the output with desired output

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.


Further Readings

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo