IF and FIND formula

[Ducky405]

The agency I'm Working for has requested a change/addition and to be honest I'm not sure it's possible.
I made a post back in October on the same topic https://www.excelforum.com/excel-for...statement.html

My original question was:

So I'm working on an intuitive schedule for the ambulance agency I work for.
I have a 4 column with headers "Crew 1" (C5), "Crew 2" (D5), "Comments" (G5) and "Status" (I5)
The formula will be in the status column and need it to search for multiple things. First, if both crew1 and crew 2 are blank, status should be "Closed"
If Crew 1 has text, but crew 2 is blank, status should be "Open Shift"
If Both crew 1 and crew 2 have text Status should say "In-Service"
Lastly, if the comments section has the word "EMS" anywhere in it I need status to say "ROA EMS"
Below is what I came up with, which works a little, but the EMS part is a little wonky.

Thanks to the response of AliGW, I began using the following:

Please Login or Register  to view this content.

This was perfect until someone needed to change it...

The same amount of columns, just different function. For ease, I have the original question posted below and the changes being made from the original are in bold.

I have a 4 column with headers "Crew 1" (C5), "Crew 2" (D5), "Comments" (G5) and "Status" (I5)
The formula will be in the status column and need it to search for multiple things.
First, if both crew1 and crew 2 are blank, status should be "Closed"
If Crew 1 has text, but crew 2 is blank, or vice versa, Crew 1 is blank, but crew 2 has text status should be "Open Shift"
If Both crew 1 and crew 2 have text Status should say "In-Service"
If the comments section has the word "Carilion EMS" anywhere in it I need status to say "Carilion EMS"
If the comments section has the word "Roanoke EMS" anywhere in it I need status to say "ROA EMS"

Again, I'm not sure if this is even possible based on the attempts I've made at this, but if anyone can help let me know! Thanks!

[azumi]

Perhaps, put this on G8 and copied down

=TRIM(IF(COUNTA(D8:E8)=0,"Closed",IF(COUNTA(D8:E8)=1,"Open Shift",IF(AND(COUNTA(D8:E8)=2,F8="shift change"),"In-Service",LEFT(F8,SEARCH("/",F8,1)-1)))))

[GeoffW283]

Azumi's solution appears to generate "Roanoke EMS" rather than the required "ROA EMS".

Here is another possibility that is a fairly small modification of Ali's original formula and generates the expected results. In H8 and copied down:

Please Login or Register  to view this content.

[Ducky405]

That worked,thank you so much! This forum never disappoints when I get stumped.

[GeoffW283]

Glad to help. Thanks for the feedback and rep.