=if(count(search({ help

[ptatlow2]

Greetings, fellow Excel users!!

I'm using Excel 2010 (PC) and have a spreadsheet in which I want to search for words in Column A, and based on what is found, populate Columns B,C,D,E accordingly.

A B C D E

STATUS	MADE	MISSED	EARLY	LATE
KALAMAZOO 74Z 06/22/2016 04:45 MISSED		1		LATE
GREENHORNE 19P 6/22/2016 05:15 MISSEDEARLY		1	EARLY	LATE

<tbody>
</tbody>

Column C has formula =IF(ISNUMBER(SEARCH("missed",A1)),"1","")
Column D has formula =IF(ISNUMBER(SEARCH("early",A1)),"EARLY","")
Column E has formula =IF(COUNT(SEARCH({"late","missed"},A1)),"LATE","") for column E. (this is because if a train is missed but not specified as late in A1, it is still late, and the LATE designation needs to be shown.)

Here's my problem... In my example, if a train is missed because it is early, I get a "1" in C2, "EARLY" in D2, and "LATE" IN E2.

So, I need a formula for Column E that basically says that if a train is missed or late, show "LATE"; but if a train is missed early, show a value in Column C and Column D, but don't show anything in E...

Any help would be greatly appreciated.

Thanks!!

Phillip

 

[ghrain22]

So just write that condition into another if statement:

=If(C2="Early","",IF(COUNT(SEARCH({"late","missed"},A1)),"LATE",""))