Forum Discussion
How to combine two complicated IF function together?
So here are the two IF functions I wish to combine together,
IF Formula 1 (Working) =IF($D$20="Left Hands",SMALL(($F$2:$G$2,$F$3:$G$3,$F$4:$G$4,$F$5:$G$5,$F$32:$G$32,$F$33:$G$33,$F$20:$G$20,$F$...
Hope,
what are the possible values in D20 and D30?
Left Hands and Right Hands
I used an solution equivalent to helper cells.
I created names:
D20_LeftHands
D20_RightHands
D30_LeftHands
D30_RightHands
Crit
D20_LeftHands is defined as:
=Sheet1!$F$2:$G$5;Sheet1!$F$32:$G$33;Sheet1!$F$20:$G$21
The others are defined accordingly.
Crit is defined as:
=IFERROR(MATCH(Sheet1!$D$20,{"Left Hands";"Right Hands"},0),0)*2+IFERROR(MATCH(Sheet1!$D$30,{"Left Hands";"Right Hands"},0),0)*3The final formula is:
=SMALL(SWITCH(Crit,2,D20_LeftHands,4,D20_RightHands,3,D30_LeftHands,6,D30_RightHands),COLUMN()-COLUMN($I$2)+1)