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

**Table of Contents**hide

**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”)**

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

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

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

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

**“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

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

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