# 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 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. Step 3: Press ENTER. Step 4: Drag the Fill Handle, Available or Not Available values will appear throughout the range. ### 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. Step 3: Press ENTER. True or False return value shows up. Step 4: Drag the Fill Handle, all the cells result in True or False like the picture below. ### 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. Step 3: Hit ENTER, restaurant value will appear. Step 4: Drag the Fill Handle, the rest of the cells get the “Available” value or remain BLANK. ### 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. Step 3: Press ENTER, text same as the criteria shows up. Step 4: Drag the Fill Handle, matching cells will show the same values as the range. ### 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. Step 3: Press CTRL+SHIFT+ENTER, as it is an array formula. The matched text for Bars appears. ### 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). 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. Step 4: Press ENTER. The matched value will appear. ### 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 “,””) 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. Step 3: Drag the Fill Handle, rest of the cells in the range the cell gets marked either by “Available ” text or remaining BLANK. ### 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 “,””) 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. Step 3: Drag the Fill Handle, the rest of the cells get marked either by “Available ” or remaining BLANK. ## 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 