Formula Reduction

rilzniak said:

I have another three formulas I'm hoping someone can review and provide feedback on:

1) Array formula: =IF($A3="","",INDEX(Inputs!$B:$S,SMALL(IF(Inputs!$B:$B=$A$1,ROW(Inputs!$B:$B)),ROW(1:1)),COLUMN(C1)))

2) Array formula: =IFERROR(INDEX(Inputs!$A:$S,SMALL(IF(Inputs!$A:$A=$A$1,ROW(Inputs!$A:$A)),ROW(1:1)),COLUMN(B1)),"")

3) =IF($A3="","",INDIRECT("'Inputs'!"&ADDRESS(MATCH($A$1,Inputs!$A:$A,0),COLUMN(C1),4)))

Suggestions on how to make any of these better?

Ryan

Click to expand...

Aladin Akyurek said:

In which cells do you want to enter these formulas?

Click to expand...

rilzniak said:

I've entered #1 in column B and dragged it down.

I've entered #2 in column A and dragged it down.

I've entered #3 in column C and dragged it down.

Click to expand...

Aladin Akyurek said:

Exact cells please for #2 cannot be i A1...

Click to expand...

rilzniak said:

1) B3:B44

2) A3:A32

3) C3:C32

I should point out that #1 is on a different worksheet than #2 and #3.

Click to expand...

=IF($A3="","",INDEX(Inputs!C$2:C$400,
  SMALL(IF(Inputs!$B$2:$B$400=$A$1,
  ROW(Inputs!$B$2:$B$400)-ROW(Inputs!$B$2)+1),
  ROWS(B$3:B3))))

=IFERROR(INDEX(Inputs!B$2:B$400,
  SMALL(IF(Inputs!$A$2:$A$400=$A$1,
  ROW(Inputs!$A$2:$A$400)-ROW(Inputs!$A$2)+1),
  ROWS(A$3:A3))),"")

Aladin Akyurek said:

Try to avoid whole column references and robustify the formulas against row/column insertions.
Care to explain how your 3rd formula is supposed to work in C3:C32?

Click to expand...

rilzniak said:

Thanks!

Hahaha, I noticed after it finished calculating that it didn't work. Here's what I came up with to replace it:

=IFERROR(INDEX(Inputs!$A$1:$S$2000,SMALL(IF(Inputs!$A$1:$A$2000=$A$1,ROW(Inputs!$A$1:$A$2000)),ROW(1:1)),COLUMN(C1)),"") with CSE.

Do you think that's a good formula? It accomplishes what I want, but I'm wondering how efficient it is.

Click to expand...

Aladin Akyurek said:

Add a few rows in front of the formula row...

Click to expand...