AND, OR, IF = all combined in 1!!

[JonesyUK]

Dear All,

Please assist: I need to create a formula that returns the value in "column I", based on the following text criteria in "column H": must contain "erl", must contain "actual", must contain ("exch" OR "fx").

I started with this: =if(and(search("erl",H3),search("actual",H3)),I3,""))) but I need to include: "exch" OR "fx"

Hope this makes sense!

Thanks....

 

[pgc01]

Hi

Try (not tested):

=if(countif(h3,"*erl*")*countif(h3,"*actual*")*(countif(h3,"*exch*")+countif(h3,"*fx*")),I3,"")

 

[JonesyUK]

This works! Many thanks,

Pure Genius....