Here I will demonstrate how to use excel "Search" function on multiple texts for "And" condition.
In the image on left side, we want to know that if the fruits are "Apple", "Mango" and "Pear", then the output should be "Category 1", else blank.Now, the combination could be in any order.
So to identify the Category 1 we will insert "Search" function along with "Count" and "If" function.
But before that we will create a list of items that are essential for Category 1 as a separate list.
And then we will apply the below formula:
=IF(COUNT(SEARCH($F$7:$F$9,A2:C2))=3,"Category 1","")
In this formula we are counting the results of search function, If its coming as "3" which means that the search function is able to search all the three fruits i.e "Apple, "Mango" and "Pear" in a row. So, after that we used an "If" function to determine that if the count is 3, then the output will be "Category 1", else it will be blank.