SOLVED

Combining 2 If Statements

Copper Contributor

Hello,

 

I am able to get either of these IF statements to work on their own, but I'm really trying to combine the two so I don't have to create additional columns in my spreadsheet to break it down into different parts. Each of these statements work on their own presently, but I can't seem to find a way to check the condition that's within that first column (J) and then look at the corresponding column (O or Q)

 

Here are my two IF statements:

=IF(ISNUMBER(SEARCH("1",J3)),IF(ISBLANK(O3),"No","Yes"))

=IF(ISNUMBER(SEARCH("7",J3)),IF(ISBLANK(Q3),"No","Yes"))

 

I think it has to do with adding in an OR somewhere when combining these formulas, but I can't seem to get it to work.

 

Any suggestions?

 

Thanks in advance!

6 Replies

@Dan_Schiller 

How about

 

 

=IF(AND(O3="",OR(ISNUMBER(SEARCH({"1","7"},J3)))),"No","Yes")

 

Thanks, but I want to see if there is a "1" in J3, then I'd like to check if there is something in the corresponding "O" cell, and if there is a "7" in J3, then I'd want to see if there is something in the corresponding "Q" cell. Based on what number is in J3, I'd want it to check the proper cell for the presence of data.

Does that make sense?

@Dan_Schiller 

My apologies - I didn't notice the O / Q difference.

=IF(OR(AND(ISNUMBER(SEARCH("1",J3)),O3=""),AND(ISNUMBER(SEARCH("7",J3)),Q3="")),"No","Yes")

@Hans Vogelaar

 

This worked, thanks a lot!

 

The only other thing I didn't think of until now, is when J3 is blank. In those instances, I'd like for the result to be "No" or "N/A". Is that something that could be accounted for? If not, your last formula will be fine

best response confirmed by Dan_Schiller (Copper Contributor)
Solution

@Dan_Schiller 

 

=IF(J3="","N/A",IF(OR(AND(ISNUMBER(SEARCH("1",J3)),O3=""),AND(ISNUMBER(SEARCH("7",J3)),Q3="")),"No","Yes"))
That's incredible, thanks again!
1 best response

Accepted Solutions
best response confirmed by Dan_Schiller (Copper Contributor)
Solution

@Dan_Schiller 

 

=IF(J3="","N/A",IF(OR(AND(ISNUMBER(SEARCH("1",J3)),O3=""),AND(ISNUMBER(SEARCH("7",J3)),Q3="")),"No","Yes"))

View solution in original post