AND and OR operator for multiple conditions

[dellzy]

Dear Excel Experts,

Please help me to understand. I have the below formula. I coded with understanding that:-

1. If in E2 meet the first condition, which is the value is "terminated", then automatically return result "Yes".
2. If E2 value is not "terminated", then moves on to the next part, which is if E2 value is "suspended" and at the same time A2 value matches one of the values in the range, it will still return "Yes", else "".

Correct me if I am wrong, if above statements are correct, why is my formula below still return error even the E2 value is "terminated"?

[/COLOR][COLOR=#333333]IF(OR(E3="terminated",AND(E3="suspended",MATCH(A3,$K$2:$K$28,0))),"Yes","")[/COLOR]

Appreciate your help.


Thank you so much.
DZ

 

[FDibbins]

almost there, try this...
=IF(E2="terminated", "Yes", IF(AND(E2="suspended",ISNUMBER(MATCH(A3,$K$2:$K$28,0))),"Yes",""))

 

[theBardd]

You don't need to repeat the result, ISNUMBER is enough

=IF(OR(E3="terminated",AND(E3="suspended",ISNUMBER(MATCH(A3,$K$2:$K$28,0)))),"Yes","")

 

[dellzy]

Hi FDibbins,

My original formula works when I put "ISNUMBER" from your solution. If you don't mind, may I ask what is the significance of ISNUMBER in this formula ya? Because earlier on I tried breaking down the condition to single IF(MATCH(A3,$K$2:$K$28,0), it gave me result even without "ISNUMBER".

Thanks a lot for your help.

 

[FDibbins]

MATCH will either return a number (if found) or an error (if not found), so I test to see if it returns a number