CODEWITHSUNDEEP
excel-formula
Aaron Nellessen
Aaron Nellessen

Reputation: 1

I want to use IF and multiple ORs with ANDs together in excel

I am having an issue on excel 2010: I want to say

IF (G3="a" AND G2>0) OR if (G3="b" AND G2>1) OR if (G3="c" AND G2>2) OR if (G3="d" AND G2>3) THEN "YES"

I have tried many attempts to code this. Any help? Here is the code I am trying to fix:

=IF(OR(AND(G3="a",F3>=1)),(AND(G3="b",F3>1)),(AND(G3="c",F3>2)),"Yes")

Upvotes: 0

Views: 1831

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

You can simplify the formula to:

=IF(AND(ISNUMBER(FIND(G3,"abcd")),F3>CODE(G3)-97),"Yes","No")

Assuming that your data provided is a simplification, here is another formula that uses INDEX,MATCH instead of AND/OR:

=IF(F3>IFERROR(INDEX({0,1,2,3},MATCH(G3,{"a","b","c","d"},0)),1E+99),"Yes","No")

Upvotes: 1

Alexis Olson
Alexis Olson

Reputation: 40244

You've got some mismatching parentheses. Try this instead of what you had.

=IF(OR(AND(G3="a",F3>=1),AND(G3="b",F3>1),AND(G3="c",F3>2)),"Yes")

This version matches your quote.

=IF(OR(AND(G3="a",G2>0),AND(G3="b",G2>1),AND(G3="c",G2>2),AND(G3="d",G2>3)),"Yes")

Upvotes: 1

Related Questions