AND and OR operator for multiple conditions

dellzy

Board Regular
Dear Excel Experts,

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"?

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

Thank you so much.
DZ

Last edited:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

FDibbins

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

theBardd

Rules violation
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

Board Regular
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

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