Compare two columns on conditions

[vvgiri]

Hi everyone,

i am bit new to excel, i am struggling to find a formula for my task. I am in process industry, and we have equipment change over schedule. on every Friday we have to check all equipment for their desired condition from actual condition and take action accordingly.
in short, if it is RUN and actual state is RUNNING, it is OK', else need to "CHANGE". If desired condition is STOP and actual is STOPPED, then OK or need to CHANGE.we have long list of equipment , so just want to compile only the equipment need to changeover.
Please find sample file.
Thanks in advance.

 

[vletm]

vvgiri
Something like this ...
ps. Take care that those Your used terms (RUN, RUNNING, STOP, STOPPED) are written just as You have express ... some of those are eg 'STOP '

 

[vvgiri]

Thanks a lot for the help, Yes the terms are captured from POIS system, I can't change.
If desired conditin is STOP and actual condition is STOPPED, then also should display OK.

 

[vletm]

vvgiri
eg cell D5,D6 'STOP' is 'STOP '; those are different.
>> D6 = 'STOP ' and E6 = 'STOPPED' then that IS NOT OK!
are ALL 'STOP' as 'STOP ' or how?
cell D9 is 'MAINT.'
every mark counts!
no matter where are those captured ... copied ...manually written...

 

[vvgiri]

Thank you very much, just out of curiosity i am asking again whether we can if formula based on cell formatting? what i observed , running cells are in Green and Stopped cells are in RED. so how to write formula , like if D4 cell colour matches with E4, Then " OK" , else " CHANGE"

 

[vletm]

STOP <> ST OP <> S TOP <> STO P ... and so on ... are different​

Green and Red ...
Have You tested conditional formatting?
With formulas, it would use 'normally' cell values,
that's means from me ... same formulas.
Formulas would be okay ... if only 'some' rows of data.
If a lot of data ... then I would use VBA.
As well as I would check cell colors with VBA (even only colors) or
whatever POIS would give.

 

[vvgiri]

Thank you for your time and advice. My issue is resolved now.

 

[Peter Bartholomew]

I am glad your problem is resolved.

For more complicated instances I believe the clearest option is to have a reference table that lists the acceptable outturn associated with each state.
In the attached I have shown how this can be used in a (growing) list context or, using array output, could be extracted to a report elsewhere in a workbook.

p.s. I like @vletm's conditional formatting.