AND and OR operator for multiple conditions

dellzy

Board Regular
Joined
Apr 24, 2013
Messages
146
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"?



Code:
[/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
 
Last edited:

Some videos you may like

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
Joined
Feb 16, 2013
Messages
6,723
almost there, try this...
=IF(E2="terminated", "Yes", IF(AND(E2="suspended",ISNUMBER(MATCH(A3,$K$2:$K$28,0))),"Yes",""))
 

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
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
Joined
Apr 24, 2013
Messages
146
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
Joined
Feb 16, 2013
Messages
6,723
MATCH will either return a number (if found) or an error (if not found), so I test to see if it returns a number
 

Forum statistics

Threads
1,089,201
Messages
5,406,810
Members
403,106
Latest member
AliO

This Week's Hot Topics

Top