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 for Download
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”)
Step 3: Press ENTER.
Step 4: Drag the Fill Handle, Available or Not Available values will appear throughout the range.
Related Content: How to Sum If Cell Contains Specific Text in Excel (6 Ways)
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
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.
Related Content: Check If Cell Contains Partial Text in Excel (5 Ways)
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
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
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
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
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
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
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.
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.