Backwards compatibility to 97-03 for IFERROR array

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-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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

a massive zebra

Board Regular
Joined
Dec 1, 2009
Messages
87
=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

New Member
Joined
Jan 30, 2014
Messages
34
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

New Member
Joined
Jan 30, 2014
Messages
34
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,397

ADVERTISEMENT

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

New Member
Joined
Jan 30, 2014
Messages
34
Marcelo, that formula also comes up with the same message about levels of nesting. Thanks anyway.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,578
Members
410,853
Latest member
Tramp71
Top