Comparing two cells and returning a text value

Hi,
I have been looking around for a formula to compare two cells for me. I have tried nested IF and AND statements but they are not really doing what I need to do.

Essentially I need to compare two cells from a seperate worksheet for the same text value, if they are the same then the text value to return is the same, else return a problem. Each of the cells I am comparing may have different text values based on conditions of other formulas in the spreadsheet.

So if I map it all out this is what it looks like:

=IF(AND('P1'!D125="Critical",'P1'!D127="Critical"),"Critical","Criticality Conflict Exists") - this works if the two cells are returning critical, however both the D125 and D127 cells can change to two other words, "important" and "defer" based on other formulas.


What I am trying to achieve is:

If the sheet P1-D125 contains the word critical/important/defer, and P1-D127 contains the word critical/important/defer, then there is a match and the cell in the active worksheet should return either the word critical or the word important, or the word defer. If they dont match then this cell should return the words criticality conflict exists.

So when I use this formula as a test to see if it works:

=IF(AND('P1'!D125="Critical",'P1'!D127="Critical"),"Critical","Criticality Conflict Exists"), IF(AND('P1'!D125="Important",'P1'!D127="Important"),"Important","Criticality Conflict Exists")

It returns #Value!

If someone could point me in the right direction / explain what I am doing wrong and what formula I should be using, that would be fantastic.. I was unsure if a lookup would suit this..

Cheers,

Matt

Perfect that worked a treat :-) Thankyou for your help....

I should be able to add in the other bit now.. I see where I was going wrong..

Thanks mate, that also worked although it did append - "conflict exists" to each value..

Cheers for all the replies