IF function when criteria is not met, it will automatically look into the next cell to verify the criteria and so on.

[kyegozun]

Hi. Can someone help me. Id like to make a IF formula that automatically check the the next cell if the statement is false.
In this example. I want to display all the company name from column A to column F if column B contains the the word "voicemail or not interested.
View attachment 85368

 

[ETAF]

what version of excel do you have
try

=FILTER(A2:B25,(B2:B25="not interested")+(B2:B25="voicemail"))

 

[bosco_yip]

Formula of using IF function+ OR function

In cell F2 enter formula and copied down

=IF(OR(B2="not interested",B2="voicemail"),A2,"")


Or, another way to give you the same result:

=IF(OR(B2={"not interested","voicemail"}),A2,"")

=IF((B2="not interested")+(B2="voicemail"),A2,"")

Regards

 

[kyegozun]

what version of excel do you have
try

=FILTER(A2:B25,(B2:B25="not interested")+(B2:B25="voicemail"))

Hi ETAF, this actually works. But now in trying to make it work with ISNUMBER(SEARCH) function to search a specific word "not interested" and "callback" that are mixed with other words but I can't make it work.
Now im using this formula:
=(INDEX($A2:$A26,SMALL(IF(ISNUMBER(SEARCH("callback",$B2:$B26)),ROW($A2:$A26)-MIN(ROW($A2:$A26))+1),ROW(A1)))
wherein it will search the word "callback". But when I try to add another word to search "not interested" the formula don't work.

But the FILTER function that you provided is working. It's just that i can't make it work with the isnumber(search) function.

 

[ETAF]

using filter
=FILTER(A2:B26,((ISNUMBER(SEARCH("not interested",B2:B26)))+ISNUMBER(SEARCH("callback",(B2:B26)))))

how do want Voicemail or is this a new report ,

see H and J column

did you want it sorted ?
=SORT(FILTER(A2:B26,((ISNUMBER(SEARCH("not interested",B2:B26)))+ISNUMBER(SEARCH("callback",(B2:B26))))))

 

[kyegozun]

using filter
=FILTER(A2:B26,((ISNUMBER(SEARCH("not interested",B2:B26)))+ISNUMBER(SEARCH("callback",(B2:B26)))))

how do want Voicemail or is this a new report ,

see H and J column

did you want it sorted ?
=SORT(FILTER(A2:B26,((ISNUMBER(SEARCH("not interested",B2:B26)))+ISNUMBER(SEARCH("callback",(B2:B26))))))

Thank you ETAF this actually works. I just can't understand the extra open parenthesis on the (B2:B26) part:
ISNUMBER(SEARCH("callback",(B2:B26))))). (highlighted with red).
Any way can I add unlimited search value using this formula? For example I also want to add "voicemail" and "passover" word to be search.

 

[ETAF]

yes can reduce the ()
=SORT(FILTER(A2:B26,(ISNUMBER(SEARCH("not interested",B2:B26))+ISNUMBER(SEARCH("callback",(B2:B26))))))

you dont have passover in the example - so i have added

=SORT(FILTER(A2:B26,(ISNUMBER(SEARCH("not interested",B2:B26))+ ISNUMBER(SEARCH("voicemail",B2:B26)) + ISNUMBER(SEARCH("passover",B2:B26)) + ISNUMBER(SEARCH("callback",(B2:B26))))))

 

[kyegozun]

thank you so much ETAF. Your a life saver.

 

[ETAF]

you are welcome