How to combine two complicated IF function together?

[Hope Diehl]

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?

[#2]

Hope,

what are the possible values in D20 and D30?

[#3]

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.

[#4]

Left Hands and Right Hands

[#5]

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)*3

The final formula is:

=SMALL(SWITCH(Crit,2,D20_LeftHands,4,D20_RightHands,3,D30_LeftHands,6,D30_RightHands),COLUMN()-COLUMN($I$2)+1)