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$21:$G$21),COLUMN()-COLUMN($I$2)+1),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),COLUMN()-COLUMN($I$2)+1))
IF Formula 2 (Working)
=IF($D$30="Left Hands",SMALL(($F$2:$G$2,$F$3:$G$3,$F$4:$G$4,$F$5:$G$5,$F$30:$G$30,$F$31:$G$31,$F$32:$G$32,$F$33:$G$33),COLUMN()-COLUMN($I$2)+1),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),COLUMN()-COLUMN($I$2)+1))
All of the cells listed in those formulas have numbers in them, and I have those numbers listed in a numerical order, using the SMALL and COLUMN functions. However it gets frustrating when I have to go in and remove cells, just because one target, D20 or D30, changes . I use these formulas at work, and I know IF can make this work easier, but right now when I combine the functions together, Excel states that I have too many arguments... And I don't quite understand how to use the nested IF functions with the functions I have... Any help?
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)
Hope,
to produce shorter formulas, which are better to evaluate, it would be better to change the model so that you can handle the data in contiguous arrays.
