Search function in MS Excel returns the position of a particular text from another text, which means it returns a number whenever it finds a text.
However, if we want to check multiple text from a string, we need to use a "Count" and "If" with "Search" function.
Just see the below example:
I have a list of days, mentioned in the below image.
We need to identify, that if the Day is either a "Saturday" or "Sunday" then the output should be "Weekend".
Now, the text for these days is not uniform across. Like - in some places Saturday is written as "Sat" or "Satday" with some extra spaces in beginning too.
To tackle this situation, we will use "Search" function, along with "Count" and "If" function
So, we will apply a formula in cell "B2" as:
=IF(COUNT(SEARCH({"Sat","Sun"},A2)),"Weekend","")
So, this formula identifies that if there are text like "Sat" or "Sun" in any cell, it will give the output as "Weekend" else it will keep it blank.
We can also apply the formula as below by using references instead of hard coding it.
This will make the formula more flexible and dynamic for future changes.
The formula will be:
=IF(COUNT(SEARCH(D2:D3,A2)),"Weekend","")
Unlike "Find", "Search" is not a case sensitive function.