Nashid
In this lesson, I’ll demonstrate 5 effective approaches to find if a cell contains text from a particular list and then how to return value in Excel. These techniques will enable you to solve the problem that led you here. You will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.
📁 Download Excel File
The sample worksheet that was used during the discussion is available to download for free right here.
Learn to Return Value If Cell Contains Text from List in Excel Using These 5 Approaches
Here I will be presenting 5 different approaches to Return a Value if Cell Contains a Text From List. Carefully go through the Steps as well as Breakdowns. Breakdowns will help you to understand the function and also the formula we have written in each method. We have attached multiple screenshots regarding the solving procedure.
1. Utilizing Combination of IF-OR and SEARCH Functions
Here a list of products is present with the type of the product itself. We are going to filter two types of products by the use of the formula stated in this approach. We are going to use the IF, OR, and IS Functions.
⬇️⬇️ STEPS ⬇️⬇️
- First, select a cell in which you want to see the filtered data.
- Copy or type in carefully the following formula.
🔨 Formula Breakdown
👉 IF(OR(ISNUMBER(SEARCH($E$6,B6)),ISNUMBER(SEARCH($E$7,B6))),B6,””)
The cell value of B6 was looked up using the SEARCH function. For “Cookies”, it has the output 11 -the starting position of the substring. For Cereal, it returns an error.
👉 IF(OR(ISNUMBER(11),ISNUMBER(SEARCH(#VALUE))),B6,””)
ISNUMBER function turned 11 and the error into a TRUE and a FALSE value respectively.
👉 IF(OR(TRUE,FALSE)),B6,””)
OR function delivers a boolean TRUE value if any of the given arguments are TRUE. Here, there is a TRUE argument. So it also delivers TRUE here.
👉 IF(TRUE, “Newtons Fig Cookies – Cookies”,””)
Because the conditions of IF is TRUE here, it delivers “Newtons Fig Cookies – Cookies” as the result.
👉 Final Output: Newtons Fig Cookies – Cookies.
- After hitting enter we will see this result.
- After using the fill tool for copying along the column look like the picture below.
We have successfully filtered only Cookies and Cereal from the list.
📕 Read More: 8 Ways to Return Value If Cell Contains Text in Excel
2. Applying TEXTJOIN, COUNTIF Formula
This function will help us get our desired result. If you don’t know yet about the TEXTJOIN formula click the link to learn. Now let’s hop into our solution.
⬇️⬇️ STEPS ⬇️⬇️
- First Select the cell you want the result into and then simply type the following formula on the formula bar.
🔨 Formula Breakdown
👉 TEXTJOIN(“, “,TRUE,IF(COUNTIF(B6,”*”&$E$6:$E$7&”*”),$E$6:$E$7,””))
As shown above, the asterisk (*) serves as the wildcard character here which looked for “Cookies” and “Cereal” substring in Cell B6 which is “Newtons Fig Cookies – Cookies” string.
👉 TEXTJOIN(“, “,TRUE,IF(COUNTIF(“Newtons Fig Cookies – Cookies”,*Cookies*, *Cereal*),$E$5:$E$6,””))
When a substring match occurred, COUNTIF function reported one. Since “Cookies” is discovered in Cell B6, it gives the array {1:0} as output.
👉 TEXTJOIN(“, “,TRUE,IF({1;0},$E$6:$E$7,””))
One value alone, “Cookies” was provided by IF function. This is because the first value of the argument (from the array) is one that is TRUE in boolean.
👉 TEXTJOIN(“, “,TRUE,{“Cookies”;””})
Due to the fact that just one of the values from the particular list was matched, the TEXTJOIN method was ineffective. If the values to compare were numerous, else it would supply each one with a comma (,) separator.
👉 Final Output: Cookies.
- Now hit Enter and use the fill tool to get the desired result.
- Use the fill tool to copy along the column.
3. Using EXACT Function with IF and TEXTJOIN Functions
In this section we will show you how to solve the “ If a cell contains text from the list then return value” problem by using EXACT, IF, and TEXTJOIN functions. Follow the instruction below,
⬇️⬇️ STEPS ⬇️⬇️
- Select a cell and type in the following formula.
🔨 Formula Breakdown
👉 EXACT(C6:C15,$E$6)
This section determines if the values in the range C6:C15 match with Cell E6 and yields TRUE or FALSE.
👉 IF(EXACT(C6:C15,$E$6),B6:B15,””)
This section provides us the names for which we receive TRUE.
👉 TEXTJOIN(“, “,TRUE,IF(EXACT(C6:C15,$E$6),B6:B15,””))
Now a comma is used after each name to complete the joining of all the names we asked for.
👉 Final Output: As we are looking for Cereal we get,“ Dr. Pepper, Nestle”.
- Now hit Enter for the result. We can see we just filtered all the Cereal’s name from the list.
4. Taking COUNTIF, IF & OR Functions to Return
Here we will be demonstrating IF, OR and COUNTIF functions to solve our problem. Let’s get started.
⬇️⬇️ STEPS ⬇️⬇️
- At the beginning select a cell and copy the following formula carefully.
=IF(OR(COUNTIF(B6,”*”&$E$6:$E$7&”*”)),B6,””)
🔨 Formula Breakdown
👉 IF(OR(COUNTIF(B6,”*”&$E$6:$E$7&”*”)),B6,””)
The asterisk (*) in this case serves as a wildcard character.. It is searching for “Cookies” and “Chips”, the substring lying in Cell B6 which is the “Newtons Fig Cookies – Cookies” string.
👉 IF(OR(COUNTIF(“Newtons Fig Cookies – Cookies”,*Cookies*, *Chips*)), B6, “”)
For each substring match, the COUNTIF Function gave back one. As it found “Cookies” in Cell B6, it returned {1:0}.
👉 IF(OR({1;0}), B6, “”)
If any of the parameters are TRUE, the OR function takes a TRUE result. In this scenario, one (1)= TRUE.
👉 IF(TRUE, “Newtons Fig Cookies – Cookies”, “”)
Since the result of the IF function is TRUE, It gives back the intended result, the first argument.
👉 Final Output: Newtons Fig Cookies – Cookies.
- Now hit Enter.
- Next, fill in the rest of the column.
5. Combining INDEX-MATCH with COUNTIF Function
Here we will be using the combination of IFERROR, INDEX, MATCH, and COUNTIF functions. An equivalent here to the TEXTJOIN formula is this. Follow the following instructions.
⬇️⬇️ STEPS ⬇️⬇️
- To start with select a cell and type in the following formula.
🔨 Formula Breakdown
👉 IFERROR(INDEX($E$6:$E$7,MATCH(1,COUNTIF(B6,”*”&$E$6:$E$7&”*”),0)),””)
In the formula, we have written up the Asterisk (*) which serves as the wildcard character. The character sought for “Cookies” and “Cereal” substring inside Cell B6 which is the “Newtons Fig Cookies – Cookies” string.
👉 IFERROR(INDEX($E$6:$E$7,MATCH(1,COUNTIF(“Newtons Fig Cookies – Cookies”,*Cookies*,*Cereal*),0)),””)
When a substring match occurred, COUNTIF function reported one. As there is “Cookies” in Cell B6, it reports {1:0}.
👉 IFERROR(INDEX($E$6:$E$7,MATCH(1,{1;0}),0)),””)
The MATCH portion of the formula reported one because there was only one matching value, “Cookies”.
👉 IFERROR(INDEX($E$6:$E$7,1),””)
As it is in the List array, the INDEX Function returned “Cookies”.
👉 IFERROR(“Cookies”,””)
In light of this, we can state that IFERROR Function is employed to manage the errors that may arise in the absence of matches.
👉 Final Output: Cookies.
- Pressing enter, we can see the following result.
- Now, use the fill tool to apply the formula to all the cells.
📕 Read More: Excel Formula: If Cell Contains Text Then Return Value in Another Cell
📄 Important Notes
🖊️ Be careful while using the code as there are a lot of terms and if you lose concentration the code may be incorrect.
🖊️ While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.
🖊️ Use $ for absolute values.
📝 Takeaways from This Article
📌 Combining functions to write formulas.
📌 COUNTIF, TEXTJOIN, SEARCH, INDEX, MATCH, IF, OR, EXACT, IS function and how they work.
📌 Breakdown of every formula used here to understand properly.
📌 Different ways of returning value if a cell contains text from list.
Conclusion
I hope you were able to use the techniques I demonstrated above in this Excel lesson if a cell contains text from list and then returns a value. As you can see, there are a lot of options on how to do this. Decide deliberately on the approach that best addresses your circumstance. I advise repeating the steps if you become confused in any of the steps if you get stuck. Practice on your own and take a look at the “If the cell contains text from the list then return value” Excel file in the practice workbook as I’ve provided above because practice makes a man perfect. I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. Again if you have any problem regarding Excel spit it in the comment box. The Excelden crew is always available to answer your questions. Keep educating yourself by reading. For more Excel-related articles please visit our website Excelden.com.
Related Articles
- VLOOKUP If Cell Contains a Word Within Text in Excel
- If One Cell Equals Another Then Return Another Cell in Excel
