Compare two columns and return value of one if the other equals specified text-CodePudding

I am trying to compare columns A and B. If A1 and B1 equal "DOES NOT EXIST", return "DOES NOT EXIST", otherwise return the value (from A or B) that does not equal "DOES NOT EXIST".

So far I've got:

=IF(AND(A1="DOES NOT EXIST", B1="DOES NOT EXIST"), "DOES NOT EXIST", ....)

But I do not know how to specify the if_false portion of the IF statement to return the value that does not equal "DOES NOT EXIST".

i.e. I'd like C1 to return "DOES NOT EXIST", C2 to return 12345, and C3 to return ABCDE.

CodePudding user response：

You can further simplify to:

=IF(A1<>"DOES NOT EXIST",A1,B1)

CodePudding user response：

Using IF, you can type it as below:

=IF(AND(A1="DOES NOT EXIST",B1="DOES NOT EXIST"),"DOES NOT EXIST",IF(A1="DOES NOT EXIST",B1,A1))

This reads as, if both A1 and B1 are equal to "DOES NOT EXIST", return "DOES NOT EXIST", otherwise, if A1 equals to "DOES NOT EXIST" return A1, otherwise, B1.