Backwards compatibility to 97-03 for IFERROR array

[chilisrool]

I have a spreadsheet I am trying to adapt so that it is backwards compatible to Excel 97-2003. Within it I use the array formula

=IFERROR(INDEX('Player Seasons'!$E:$E,SMALL(IF(FREQUENCY(IF('Player Seasons'!$D$2:$D$358="REG",MATCH('Player Seasons'!$E$2:$E$358,'Player Seasons'!$E$2:$E$358,0)),ROW('Player Seasons'!$E$2:$E$358)-ROW('Player Seasons'!$E$2)+1),ROW('Player Seasons'!$E$2:$E$358)),ROWS(AE$2:AE2))),"")

in order to compile a list of names that fulfill certain criteria. Can anyone suggest an alternative that will work in earlier versions of excel?

Thanks in advance.

 

[a massive zebra]

=IF(ISERROR(INDEX('Player Seasons'!$E:$E,SMALL(IF(FREQUENCY(IF('Player Seasons'!$D$2:$D$358="REG",MATCH('Player Seasons'!$E$2:$E$358,'Player Seasons'!$E$2:$E$358,0)),ROW('Player Seasons'!$E$2:$E$358)-ROW('Player Seasons'!$E$2)+1),ROW('Player Seasons'!$E$2:$E$358)),ROWS(AE$2:AE2)))),"",INDEX('Player Seasons'!$E:$E,SMALL(IF(FREQUENCY(IF('Player Seasons'!$D$2:$D$358="REG",MATCH('Player Seasons'!$E$2:$E$358,'Player Seasons'!$E$2:$E$358,0)),ROW('Player Seasons'!$E$2:$E$358)-ROW('Player Seasons'!$E$2)+1),ROW('Player Seasons'!$E$2:$E$358)),ROWS(AE$2:AE2))))

 

[chilisrool]

That is great, thanks.

I just realised I have to include an extra condition though, which segment of the code should I replicate in order to include that? Should I add an extra condition inside the IF func that lies inside the FREQUENCY func in both parts of the formula?

 

[chilisrool]

I tried but excel had a problem with the required levels of nesting. The new formula I am looking to adapt is:

=IFERROR(INDEX('Player Seasons'!$E:$E,SMALL(IF(FREQUENCY(IF('Player Seasons'!$C$2:$C$358="A",IF('Player Seasons'!$D$2:$D$358="REG",MATCH('Player Seasons'!$E$2:$E$358,'Player Seasons'!$E$2:$E$358,0))),ROW('Player Seasons'!$E$2:$E$358)-ROW('Player Seasons'!$E$2)+1),ROW('Player Seasons'!$E$2:$E$358)),ROWS(AE$4:AE4))),"")

 

[Marcelo Branco]

I tried but excel had a problem with the required levels of nesting. The new formula I am looking to adapt is:

=IFERROR(INDEX('Player Seasons'!$E:$E,SMALL(IF(FREQUENCY(IF('Player Seasons'!$C$2:$C$358="A",IF('Player Seasons'!$D$2:$D$358="REG",MATCH('Player Seasons'!$E$2:$E$358,'Player Seasons'!$E$2:$E$358,0))),ROW('Player Seasons'!$E$2:$E$358)-ROW('Player Seasons'!$E$2)+1),ROW('Player Seasons'!$E$2:$E$358)),ROWS(AE$4:AE4))),"")

This should work in Excel 2003

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX('Player Seasons'!$E:$E,SMALL(IF(FREQUENCY(IF('Player Seasons'!$C$2:$C$358="A",IF('Player Seasons'!$D$2:$D$358="REG",MATCH('Player Seasons'!$E$2:$E$358,'Player Seasons'!$E$2:$E$358,0))),ROW('Player Seasons'!$E$2:$E$358)-ROW('Player Seasons'!$E$2)+1),ROW('Player Seasons'!$E$2:$E$358)),ROWS(AE$4:AE4)))))

Ctrl+Shift+Enter

M.

 

[chilisrool]

Marcelo, that formula also comes up with the same message about levels of nesting. Thanks anyway.

 

[chilisrool]

Can anyone else offer a solution that will work in Excel 97?