How to MATCH EXACTLY 2 values & get the desired result

[hsandeep]

Column D5:D9999 contains either of 5 values: ABC, MNO, XYZ, #N/A, null
Column E5:E9999 contains either of 4 values: WORKING, NOT WORKING, #N/A, null.
In F5:F9999, I want value of Z1 if column D MATCHES EXACTLY as X1 AND column E MATCHES EXACTLY as Y1 else AB1.
Ex:
Z1=Good, X1=ABC, Y1=WORKING, AB1=Bad
For D5=ABC AND E5=WORKING
F5=Good (Answer)

Z1=Good, X1=ABC, Y1=WORKING, AB1=Bad
For D5=ABC AND E5=WOrkiNG
F5=Bad (Answer)

 

[circledchicken]

Hi,

Maybe try something like this:

=IF(AND(D5=X$1,ISNUMBER(FIND(D5,X$1)),E5=Y$1,ISNUMBER(FIND(E5,Y$1))),Z$1,AB$1)

To handle the #N/A's properly it might be easier to remove them from the source columns in the first place.

 

[Rick Rothstein]

Column D5:D9999 contains either of 5 values: ABC, MNO, XYZ, #N/A, null
Column E5:E9999 contains either of 4 values: WORKING, NOT WORKING, #N/A, null.

Is the #N/A text that you typed in or is it an error result from a formula? Also, is null a text word or is it meant to mean the empty string ("") in the cell? If it is a text word, it was meant to be all lower case as you show (which is different than the all upper case you show for the other words), correct?

 

[Rick Rothstein]

Column D5:D9999 contains either of 5 values: ABC, MNO, XYZ, #N/A, null
Column E5:E9999 contains either of 4 values: WORKING, NOT WORKING, #N/A, null.

Is the #N/A text that you typed in or is it an error result from a formula? Also, is null a text word or is it meant to mean the empty string ("") in the cell? If it is a text word, it was meant to be all lower case as you show (which is different than the all upper case you show for the other words), correct?

 

[hsandeep]

#N/A it is an error result from a formula.
null means the empty string ("") in the cell

 

[Rick Rothstein]

#N/A it is an error result from a formula.
null means the empty string ("") in the cell

One other question... is it possible for #N/A to be on one column but not the other? In other words, could D2 be #N/A when E2 is WORKING, NOT WORKING or null? Or could D2 be ABC, MNO, XYZ or null when E2 is #N/A?