Formula Reduction

[rilzniak]

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

 

[markmzz]

A small modification:

1) =IF($A3="","",IFERROR(INDEX(Inputs!$C$1:$S$2000,SMALL(IF(Inputs!$B$1:$B$2000=$A$1,ROW(Inputs!$B$1:$B$2000)-ROW(Inputs!$B$1)+1),ROWS(B$3:B3)),COLUMNS($B3:B3)),""))

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

3) =IF($A3="","",INDIRECT("'Inputs'!"&ADDRESS(MATCH($A3,Inputs!$A$1:$A$2000,0),COLUMNS($C3:C3)+COLUMN(Inputs!$C$1)-1)))

Markmzz

 

[rilzniak]

A3, control+shift+enter, not just enter, copy down, and across:

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

Awesome! Thanks! I'm still trying to learn Excel. Any valuable advice you can share on where to learn, or what to read?

 

[Aladin Akyurek]

Awesome! Thanks! I'm still trying to learn Excel. Any valuable advice you can share on where to learn, or what to read?

This forum. Study the questions with the appropriate responses, and try to dig up similar ideas for review...