Priti
Excel is an outstanding data analysis and visualization tool. Sometimes we require a value returned in a specific cell if that cell contains the text for data analysis or visualization purposes. As a result, this article will demonstrate 8 easy ways to return the value if the cell contains text in Excel.
📁 Download Excel File
Download the Excel file used for the demonstration from the link below.
Learn to Return Value If Cell Contains Text in Excel with These 8 Ways
This article employs distinct functions to return the value if the cell contains the text. We have used IF, VLOOKUP, EXACT, COUNTIF, AND, OR, ISNUMBER, SEARCH, INDEX, and MATCH functions. Let’s explore the Excel formulae and ways for returning the value if the cell contains a text.
1. Using IF Function
IF function logically compares a value and your desired outcome and can return two results: one is if TRUE, and another one is FALSE. This method will use the IF function to return a value if the cell contains a text. For that, we have a data set containing the Product Name and the City Name as the delivery place. Now we want to know if it will be delivered to California.
Let us see the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, insert the formula in cell D6.
=IF(C6=”California”,”Yes”,”No”)
Here, this part C6=”California” is the logical test part that compares the text string in cell D6 with California. If the text string in cell D6 matches, it returns “Yes” and otherwise, “No”.
- Then drag the Fill Handle down to the last row and get the result.
2. Applying VLOOKUP Function
VLOOKUP function finds something in a table or a range by row. This method will use this function to return a value if a cell contains a text. Here we will try to find the delivery place of a specific product using this function.Let us see go through the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell C13 and enter the formula.
=VLOOKUP(C12,B6:C10,2,FALSE)
In this function, the first C12 contains the value that we have to look up. B6:C10 is the array where the function will search for the lookup value, and 2 shows that we have to look up in the second column of the array, and FALSE represents that it needs to be an exact match.
📕 Read More: VLOOKUP If Cell Contains a Word Within Text in Excel
3. Utilizing INDEX and MATCH Functions
This method will utilize INDEX and MATCH functions to return value if the cell contains a text. Here we will try to find the delivery place of a specific product using this function.
Let us see go through the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell C13 and enter the formula.
=INDEX(C6:C10,MATCH(C12,B6:B10,0))
🔨 Formula Breakdown
Here, the formula inserted in C13 is:
INDEX(C6:C10,MATCH(C12,B6:B10,0))
👉 Here MATCH function searches for a value in C12 in the range B6:B10 and returns the relative position of the item.
👉 On the other hand, INDEX function returns the value in the range C6:C10 for the specified reference in the MATCH function.
4. Employing IF and COUNTIF Functions
IF function logically compares a value and your desired outcome and can return two results: one is if TRUE, and another one is FALSE. On the other hand, the COUNTIF function counts the number of cells that meets a specific criterion. This method will use the IF and COUNTIF functions to return a value if a cell contains a text. For that, we have a data set containing the Product Name and the City Name as the delivery place. Now we want to know if it will be delivered to California.
Let us see the steps.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, insert the formula in cell D6.
=IF(COUNTIF(C6,”*California*”),”Yes”,”No”)
🔨 Formula Breakdown
Here, the formula inserted in D6 is:
IF(COUNTIF(C6,”*California*”),”Yes”,”No”)
👉 COUNTIF(C6,”*California*”) part counts the number of cells that contains “California”.
👉 Now, if COUNIF function returns a result then IF function returns “Yes” and otherwise “No”.
- After then, drag the Fill Handle down to the final row to obtain the outcome.
5. Using ISNUMBER and SEARCH Functions
The ISNUMBER function checks a number value and returns TRUE or FALSE. On the other hand, SEARCH function searches the text string in another text string and returns the number of the starting position of the first text string from the first character of the second text string.This method will demonstrate how to use these two functions to return value if a cell contains a text.
Let us see the steps.
⬇️⬇️ STEPS ⬇️⬇️
- Select cell D6 and insert the formula and we will have our outcome.
=ISNUMBER(SEARCH(“California”,$C$6:$C$10))
🔨 Formula Breakdown
Here, the formula inserted in D6 is:
ISNUMBER(SEARCH(“California”,$C$6:$C$10))
👉 SEARCH(“California”,$C$6:$C$10) searches “California” in the range $C$6:$C$10.
👉 Then, the ISNUMBER function returns TRUE or FALSE depending on the output of SEARCH function.
6. Applying IF and EXACT Functions
IF function logically compares a value and your desired outcome and can return two results: one is if TRUE, and another one is FALSE. On the other hand, the EXACT function returns TRUE comparing two text strings if they match exactly; FALSE otherwise. This method will use the IF and EXACT functions to return a value if a cell contains a text. Let us have a look at the steps.
⬇️⬇️ STEPS ⬇️⬇️
- At first, insert the formula in cell D6.
=IF(EXACT(C6,”New York”),”Yes”,”No”)
🔨 Formula Breakdown
Here, the formula inserted in D6 is:
IF(EXACT(C6,”New York”),”Yes”,”No”)
👉 EXACT(C6,”New York”) compares text in cell C6 with “New York”.
👉 Now, IF function returns “Yes” if the EXACT function finds an exact match; otherwise, “No”.
- Lastly, drag the Fill Handle down to the final row to obtain the results.
7. Combining IF with OR Function
IF function logically compares a value and your desired outcome and returns TRUE, or FALSE. The OR function determines if any conditions in a test are TRUE. In addition, the ISNUMBER function checks a number value and returns TRUE or FALSE. On the other hand, SEARCH function searches the text string in another text string and returns the number of the starting position of the first text string from the first character of the second text string.
In this method, we will use all of these functions to return value if cell contains text. Let us see the steps.
⬇️⬇️ STEPS ⬇️⬇️
- Select cell D6 and insert the formula.
=IF(OR(ISNUMBER(SEARCH(“California”,C6)),ISNUMBER(SEARCH(“Texas”,C6))),”Yes”,”No”)
🔨 Formula Breakdown
Here, the formula inserted in D6 is:
IF(OR(ISNUMBER(SEARCH(“California”,C6)),ISNUMBER(SEARCH(“Texas”,C6))),”Yes”,”No”)
👉 SEARCH(“California”,C6) searches “California” in the cell C6 and SEARCH(“Texas”,C6) searches “Texas” in the cell C6.
👉 Then, the ISNUMBER function returns TRUE or FALSE depending on the output of SEARCH function.
👉 Next, OR function returns output for both arguments if they are TRUE.
👉 Lastly, IF function shows “Yes” or “No” according to the output from OR function.
- Finally, to get the outcome, drag the Fill Handle down to the last row.
8. Combining IF with AND Function
The IF function logically compares a value and your desired outcome and returns TRUE, or FALSE. The AND function determines if all conditions in a test are TRUE. In addition, the ISNUMBER function checks a number value and returns TRUE or FALSE. On the other hand, SEARCH function searches the text string in another text string and returns the number of the starting position of the first text string from the first character of the second text string.
In this method, we will use all of these functions to return a value if a cell contains a text. Let us see the steps.
⬇️⬇️ STEPS ⬇️⬇️
- Select cell D6 and insert the formula.
=IF(AND(ISNUMBER(SEARCH(“New York”,C6)),ISNUMBER(SEARCH(“New”,C6))),”Yes”,”No”)
🔨 Formula Breakdown
Here, the formula inserted in D6 is:
IF(AND(ISNUMBER(SEARCH(“New York”,C6)),ISNUMBER(SEARCH(“New”,C6))),”Yes”,”No”)
👉 SEARCH(“New York”,C6) searches “New York” in the cell C6 and SEARCH(“New”,C6) searches “New” in the cell C6.
👉 Then, the ISNUMBER function returns TRUE or FALSE depending on the output of SEARCH function.
👉 Next, AND function returns the output of all TRUE arguments.
👉 Lastly, IF function shows “Yes” or “No” according to the output from AND function.
- Lastly, drag the Fill Handle down to the last row to see the outcome.
📝 Takeaway from This Article
📌 This article demonstrated 8 formulas to return value if a cell contains text in Excel.
📌 In the first method, we used IF function for returning value if the cell contains text in Excel.
📌 Then, we applied VLOOKUP function to return a value if a cell contains a text in Excel.
📌 After that, we demonstrated INDEX and MATCH functions for returning value if the cells contain text in Excel.
📌 Next, we employed IF and COUNTIF functions to return a value if the cell contains text in Excel.
📌 Then, we applied ISNUMBER and SEARCH functions for returning value if the cell contains text in Excel.
📌 After that, we demonstrated IF and EXACT functions to return value if cells contain text in Excel.
📌 Next, we employed IF, OR, ISNUMBER, and SEARCH functions for returning values if cells contain text in Excel.
📌 Finally, we combined IF, AND, ISNUMBER and SEARCH functions to return the value if the cell contains text in Excel.
Conclusion
This article has demonstrated 8 formulas to return value if a cell contains text in Excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.
For more guides like this, visit Excelden.com.
Related Articles
- If Cell Contains Text From List Then Return Value in Excel
- Excel Formula: If Cell Contains Text Then Return Value in Another Cell
- If One Cell Equals Another Then Return Another Cell in Excel
