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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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,257
Messages
5,546,806
Members
410,759
Latest member
Bufnercash
Top