chilisrool
New Member
- Joined
- Jan 30, 2014
- Messages
- 34
I have a spreadsheet I am trying to adapt so that it is backwards compatible to Excel 97. Within it I use the array formula
=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))),"")
in order to compile a list of names that fulfill certain criteria.
I have previously tried using =IF(ISERROR... and =LOOKUP(REPT(...),CHOOSE... but both of these methods return errors about the required levels of nesting. Can anyone suggest an alternative that will work in Excel 97?
Thanks in advance.
=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))),"")
in order to compile a list of names that fulfill certain criteria.
I have previously tried using =IF(ISERROR... and =LOOKUP(REPT(...),CHOOSE... but both of these methods return errors about the required levels of nesting. Can anyone suggest an alternative that will work in Excel 97?
Thanks in advance.