Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

How to combine 3 IF formulas with 3 different value_if_false results into 1 formula

Copper Contributor

I have a matrix, 15 rows (A), 10 columns (B,C,D..). In a result should get a table returning 6 different results.

Need ONLY 1 formula to fit each cell.

Managed to create 3, which now need to be combined - not succeeding on my own.

=IF(AND($A2<=5;OR(B$1<=3;B$1>=8));13;14)

=IF(AND($A2>=6;$A2<=10;OR(B$1<=3;B$1>=8));11;12)

=IF(AND($A2>=11;OR(B$1<=3;B$1>=8));9;10)

2 Replies
best response confirmed by Ppetu999 (Copper Contributor)
Solution

@Ppetu999 

Hi, this should work like this:

=IF($A2<=5,IF(OR(B$1<=3,B$1>=8),13,14),IF($A2<=10,IF(OR(B$1<=3,B$1>=8),11,12),IF($A2>=11,IF(OR(B$1<=3,B$1>=8),9,10))))

or with ";":
=IF($A2<=5;IF(OR(B$1<=3;B$1>=8);13;14);IF($A2<=10;IF(OR(B$1<=3;B$1>=8);11;12);IF($A2>=11;IF(OR(B$1<=3;B$1>=8);9;10))))

 

Alternatively, a bit shorter you could do a combination with LOOKUP().

=XLOOKUP(A2,{5;10;11},{13;11;9},9,1)+IF(OR(B$1<=3,B$1>=8),0,1)

or with ";":
=XLOOKUP(A2;{5.10.11};{13.11.9};9;1)+IF(OR(B$1<=3;B$1>=8);0;1)

Please try if this fits.

Great, the 1st version with ";" works for me well! Thanks a lot!
1 best response

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

@Ppetu999 

Hi, this should work like this:

=IF($A2<=5,IF(OR(B$1<=3,B$1>=8),13,14),IF($A2<=10,IF(OR(B$1<=3,B$1>=8),11,12),IF($A2>=11,IF(OR(B$1<=3,B$1>=8),9,10))))

or with ";":
=IF($A2<=5;IF(OR(B$1<=3;B$1>=8);13;14);IF($A2<=10;IF(OR(B$1<=3;B$1>=8);11;12);IF($A2>=11;IF(OR(B$1<=3;B$1>=8);9;10))))

 

Alternatively, a bit shorter you could do a combination with LOOKUP().

=XLOOKUP(A2,{5;10;11},{13;11;9},9,1)+IF(OR(B$1<=3,B$1>=8),0,1)

or with ";":
=XLOOKUP(A2;{5.10.11};{13.11.9};9;1)+IF(OR(B$1<=3;B$1>=8);0;1)

Please try if this fits.

View solution in original post