Arrays: How to make this formula work to produce info into four columns

[Jtrabe]

Hello,
I am bringing in a list using an array function. I use this function without an array it does the job, but when I actually make it do what it is supposed to do as an array it only works for the first column. Can anyone help to make this work so column b, c, and d produce information?

Thank you.

---

{=IF(ISERROR(INDEX(SS!$B$5:$B$825,SMALL(IF(SS!$A$5:$A$825='DA'!$B$3,ROW(INDIRECT("1:"&ROWS(SS!$A$5:$A$825)))),ROW(INDIRECT("1:"&ROWS(SS!$A$5:$A$825)))))),"",INDEX(SS!$B$5:$B$825,SMALL(IF(SS!$A$5:$A$825='DA'!$B$3,ROW(INDIRECT("1:"&ROWS(SS!$A$5:$A$825)))),ROW(INDIRECT("1:"&ROWS(SS!$A$5:$A$825))))))}

 

[markmzz]

Maybe one of this array formulas (use Ctrl+Shift+Enter and not only Enter):

D3-> =IF(ISERROR(INDEX(SS!$B$5:$B$825,SMALL(IF(SS!$A$5:$A$825=DA!$B$3,ROW(INDIRECT("1:"&ROWS(SS!$A$5:$A$825)))),ROWS($D$3:D3)))),"",
INDEX(SS!$B$5:$B$825,SMALL(IF(SS!$A$5:$A$825=DA!$B$3,ROW(INDIRECT("1:"&ROWS(SS!$A$5:$A$825)))),ROWS($D$3:D3))))

Or

D3-> =IF(ISERROR(INDEX(SS!$B$5:$B$825,SMALL(IF(SS!$A$5:$A$825=DA!$B$3,ROW(INDIRECT("1:"&ROWS(SS!$A$5:$A$825)))),COLUMNS($D$3:D3)))),"",
INDEX(SS!$B$5:$B$825,SMALL(IF(SS!$A$5:$A$825=DA!$B$3,ROW(INDIRECT("1:"&ROWS(SS!$A$5:$A$825)))),COLUMNS($D$3:D3))))

Markmzz

 

[Jtrabe]

Hi Markmzz,

For some reason that continues to still pull only the first column from SS. Any other ideas are welcomed!

Thanks,
J

 

[Andrew Poulsom]

Don't you just need to remove some $ signs so that the references to column B aren't absolute?

 

[Jtrabe]

I'm trying to pull from SS column 'B' into DA Column A, SS column 'C' into DA Column B, SS column 'D' into DA Column 'C' etc. Based on SS column 'A' and the DA $B$3.

 

[Jtrabe]

I am getting a repeat of line one from SS. Any ideas on that one?

 

[Jtrabe]

Here is a link to the document that I am trying to produce. I'm stuck and a newbie when it comes to arrays. I appreciate the help!
https://docs.google.com/file/d/0B92MiuaPhFAiOVlaZXZfWDJ0RzA/edit?usp=sharing
Thanks

 

[markmzz]

Here is a link to the document that I am trying to produce. I'm stuck and a newbie when it comes to arrays. I appreciate the help!
https://docs.google.com/file/d/0B92MiuaPhFAiOVlaZXZfWDJ0RzA/edit?usp=sharing
Thanks

Andrew is right. Try this small modification in my last formula:

=IF(ISERROR(INDEX(SS!B$5:B$825,SMALL(IF(SS!$A$5:$A$825=DA!$B$3,ROW(INDIRECT("1:"&ROWS(SS!$A$5:$A$825)))),ROWS($A$5:A5)))),"",
INDEX(SS!B$5:B$825,SMALL(IF(SS!$A$5:$A$825=DA!$B$3,ROW(INDIRECT("1:"&ROWS(SS!$A$5:$A$825)))),ROWS($A$5:A5))))

By the way, the Style Sheet is DA_SP14Styles or DA_SU14Styles?

Markmzz

 

[markmzz]

You can use this new version:

=IFERROR(INDEX(SS!B$5:B$825,SMALL(IF(SS!$A$5:$A$825=$B$3,ROW(SS!$A$5:$A$825)-ROW(SS!$A$5)+1),ROWS($A$5:A5))),"")

Markmzz

 

[Jtrabe]

Andrew is right. Try this small modification in my last formula:

=IF(ISERROR(INDEX(SS!B$5:B$825,SMALL(IF(SS!$A$5:$A$825=DA!$B$3,ROW(INDIRECT("1:"&ROWS(SS!$A$5:$A$825)))),ROWS($A$5:A5)))),"",
INDEX(SS!B$5:B$825,SMALL(IF(SS!$A$5:$A$825=DA!$B$3,ROW(INDIRECT("1:"&ROWS(SS!$A$5:$A$825)))),ROWS($A$5:A5))))

By the way, the Style Sheet is DA_SP14Styles or DA_SU14Styles?

Markmzz

By the way, the Style Sheet is DA_SP14Styles or DA_SU14Styles?
- This is a drop down to select the between season styles. When I pull the code down to row 6 7 8 9. I and make it an array it continues to only populate row 5 from the style sheet.