Excel If Cell Contains Text Then Return Value (8 Easy Ways)

Excel has various built-in functions to match and fetch value if a cell contains text or string. In many cases, we try to search for a text or string in a range in Excel. Particular functions in Excel return value if cells contain specific/exact text or string. In this article, we use IF, ISNUMBER, EXACT, COUNTIF, INDEX, MATCH, VLOOKUP, SEARCH, OR, and AND functions to return value if cell contains text or string.

In a situation, suppose we have entries of certain categories and products in a dataset like the image below

dataset

Dataset for Download

Excel if Cell Contains Text Then Return Value.xlsx

8 Easy Ways to Excel if Cell Contains Text Then Return Value

Method 1: Using IF Function (Cell Contains Specific Text)

The syntax of the IF function is =IF (logical_test, [value_if_true], [value_if_false]). It results in one pre-selected value either [value_if_true] or [value_if_false] depending on the logical_test output; true or false respectively.

Step 1: Click on any blank cell (D4).

Step 2: Insert the formula

=IF (B4=”Bars”,”Available”,”Not Available”)

 Here, the logical_test is to match Bars text in cell B4; if the test is true it results in Available, otherwise Not Available.

if function

Step 3: Press ENTER.

if function result

Step 4: Drag the Fill Handle, Available or Not Available values will appear throughout the range.

final result if function

Method 2: Using ISNUMBER and SEARCH Function (Cells Contain Specific Text)

The ISNUMBER function returns true or false depending on a match of SEARCH text in an absolute range.

Step 1: Click on any blank cell (D4).

Step 2: Enter the formula

=ISNUMBER (SEARCH (“Bars”, $B$4:$B$12))
 In the formula, the SEARCH function matches the text “Bars” in an absolute range then returns True or False depending on the match.

isnumber and search function

Step 3: Press ENTER. True or False return value shows up.

isnumber and search result

Step 4: Drag the Fill Handle, all the cells result in True or False like the picture below.

final result isnumber and search

Method 3: Using IF and Exact Function (Cells Contain Exact Text)

If we consider the text as case-sensitive and want an exact match, we can use the EXACT function combined with the IF function.

Step 1: Select a blank cell (D4).

Step 2: Paste the formula

=IF(EXACT(B4,”Bars”), “Available”, “”)
Inside the formula, the EXACT function matches the exact text “Bars” in cell B4 then returns the value “Available” otherwise BLANK the cell depending on an exact match.

if and exact function

Step 3: Hit ENTER, restaurant value will appear.

if and exact function result

Step 4: Drag the Fill Handle, the rest of the cells get the “Available” value or remain BLANK.

final if and exact function

Method 4: Using IF and COUNTIF Function

Combined IF and COUNTIF function returns the same cell text as it matches criteria in a range.

Step 1: Click on a blank cell (D4).

Step 2: Copy and paste the formula

=IF(COUNTIF(B4,”*Bars*”),B4,””)
In the formula, the COUNTIF function matches the criteria “*Bars*” (the formula automatically puts * both sides of the criteria) in range (cell B4). Then it returns the value in B4 otherwise keeps the cell BLANK.

if and countif function

Step 3: Press ENTER, text same as the criteria shows up.

if and countif result

Step 4: Drag the Fill Handle, matching cells will show the same values as the range.

final result if and countif

Method 5: Using INDEX and MATCH Function

Sometimes, we have a criterion in a range of cells to match the result in another range of cells. In that case, we can use the INDEX function to match a text in a range and the MATCH function to result in the value in another cell. To achieve the purpose, we slightly alter the dataset.

Step 1: Select any blank cell (B2).

Step 2: Type the formula

=INDEX(C7:C15,MATCH(“Bars”,B7:B15,0))
Here INDEX function looks for the exact match text “Bars” from the range B7:B15 in the range C7:C15.

index and match function

Step 3: Press CTRL+SHIFT+ENTER, as it is an array formula. The matched text for Bars appears.

final index and match

Method 6: Using VLOOKUP Function

The VLOOKUP function is efficient to find vertical data in a table. In our case, we can use the VLOOKUP function to find an exact or approximate match in a column. The syntax of the VLOOKUP function is =VLOOKUP (value, table, col_index, [range_lookup]).

Step 1: Type the lookup text (Bars) in any cell (B3).

vlookup function

Step 2: Select any blank cell(C3).

Step 3: Insert the formula

=VLOOKUP(B3,B7:C15,2,FALSE)
Here “Bars” is the text in B3 that has to match within a range B7:C15 to a value in column 2. FALSE declares we want an exact match.

vlookup function formula

Step 4: Press ENTER. The matched value will appear.

vlookup result final

Method 7: Using IF OR ISNUMBER and SEARCH Function (Cells Contain Strings)

A dataset often contains more than one text string. We want to match the cells that have only one match text string. We can use ISNUMBER and SEARCH to match a text, then OR function to declare an alternative match. At last, IF functions to show a return value otherwise the cells remain BLANK.

Step 1: Select any blank cell (D4). Type the formula

=IF(OR(ISNUMBER(SEARCH(“Bars”,B4)),ISNUMBER(SEARCH(“Veg”,B4))),”Available “,””)

find text depending on any one text string

Step 2: Hit ENTER. “Available ” text shows up in the cell in case any of the reference texts (“Bars” or“Veg”) exist in cell B4.

find text depending on any one test string

Step 3: Drag the Fill Handle, rest of the cells in the range the cell gets marked either by “Available ” text or remaining BLANK.

find text depending on any one text string result

 

Method 8:  Using IF AND ISNUMBER and SEARCH Function (Cells Contain Strings)

From Method 7, we know how multiple text strings in a cell get matched. If we go further to match both text strings, we can use AND function instead of OR.

Step 1: Choose any blank cell (D4). Type the formula

=IF(AND(ISNUMBER(SEARCH(“Bars”,B4)),ISNUMBER(SEARCH(“Chlt”,B4))),”Available “,””)

find text depending on multiple text string

Step 2: Hit ENTER. If both of the text strings exist in cell B4, the formula returns “Available ” as a value otherwise the cells remain BLANK.

result find text depending on multiple text string

Step 3: Drag the Fill Handle, the rest of the cells get marked either by “Available ” or remaining BLANK.

final result find text depending on multiple text string

Conclusion

In this article, we use various formulas to return value if cells contain certain texts. We use IF, ISNUMBER, EXACT, INDEX, MATCH, OR, and AND functions to return value for a text’s exact or approximate match. We also show methods to match more than one string combining IF, AND, ISNUMBER, and SEARCH functions. Hope you find discussed methods super easy to follow. Comment, if you need further clarifications or have something to add.

Maruf Islam

I, Maruf Islam, an engineer, content writer. I completed my BSc from Bangladesh University Of Engineering & Technology, want to pursue a career in content writing & development.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo