# Backwards compatibility to Excel 97 for IFERROR array

#### chilisrool

##### New Member
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?

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### FDibbins

##### Well-known Member
Apart from the IFERROR() I see no other functions that wont work in 97. Keep in mind that is an ARRAY formula and needs to be entered using CTRL SHT ENTER, not just enter.

Having said that, give this a try...
=IF(ISERROR(
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))),"",
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))))

#### chilisrool

##### New Member
I get the same error as before. It reads:

"The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format."

#### FDibbins

##### Well-known Member
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))),"",

I only see 4-deep nesting (1 + 3 levels and then 3 levels). Perhaps try using a helper column to combine C and D =C2&"-"&D2, and then test for "A-REG", that will eliminate 1 of the IF's

#### chilisrool

##### New Member

Thanks, the helper column worked for me.

Happy to help

#### CMMilne

##### New Member

Hello - I am trying to make the following IFERROR backwards compatible and I am running into problems. I tried changing it to IF(ISERROR... but I keep getting "too many arguments" and I try to adjust and I keep getting into a circular loop. Any suggestions:

(IFERROR(VLOOKUP(CONCATENATE('Input Sheet'!\$C\$8,'InputSheet'!\$C\$10,IFERROR(MIN(12,DATEDIF('InputSheet'!\$C\$6,DATE(B3,1,1),"Y")+1),0)),INDIRECT('InputSheet'!\$C\$8&"pay"),HLOOKUP('Input Sheet'!\$C\$9,INDIRECT('InputSheet'!\$C\$8&"pay"),2,FALSE),FALSE),0)*'InputSheet'!\$C\$11*12*(DATE(B3,MONTH('Input Sheet'!\$C\$6),DAY('InputSheet'!\$C\$6))-DATE(B3,1,1))/365.25)+(VLOOKUP(CONCATENATE('InputSheet'!\$C\$8,'Input Sheet'!\$C\$10,MIN(12,DATEDIF('InputSheet'!\$C\$6,DATE(B3,12,31),"Y")+1)),INDIRECT('Input Sheet'!\$C\$8&"pay"),HLOOKUP('InputSheet'!\$C\$9,INDIRECT('InputSheet'!\$C\$8&"pay"),2,FALSE),FALSE)*'InputSheet'!\$C\$11*12*(1-(DATE(B3,MONTH('Input Sheet'!\$C\$6),DAY('InputSheet'!\$C\$6))-DATE(B3,1,1))/365.25))

#### Joe4

Hello - I am trying to make the following IFERROR backwards compatible and I am running into problems. I tried changing it to IF(ISERROR... but I keep getting "too many arguments" and I try to adjust and I keep getting into a circular loop. Any suggestions:

(IFERROR(VLOOKUP(CONCATENATE('Input Sheet'!\$C\$8,'InputSheet'!\$C\$10,IFERROR(MIN(12,DATEDIF('InputSheet'!\$C\$6,DATE(B3,1,1),"Y")+1),0)),INDIRECT('InputSheet'!\$C\$8&"pay"),HLOOKUP('Input Sheet'!\$C\$9,INDIRECT('InputSheet'!\$C\$8&"pay"),2,FALSE),FALSE),0)*'InputSheet'!\$C\$11*12*(DATE(B3,MONTH('Input Sheet'!\$C\$6),DAY('InputSheet'!\$C\$6))-DATE(B3,1,1))/365.25)+(VLOOKUP(CONCATENATE('InputSheet'!\$C\$8,'Input Sheet'!\$C\$10,MIN(12,DATEDIF('InputSheet'!\$C\$6,DATE(B3,12,31),"Y")+1)),INDIRECT('Input Sheet'!\$C\$8&"pay"),HLOOKUP('InputSheet'!\$C\$9,INDIRECT('InputSheet'!\$C\$8&"pay"),2,FALSE),FALSE)*'InputSheet'!\$C\$11*12*(1-(DATE(B3,MONTH('Input Sheet'!\$C\$6),DAY('InputSheet'!\$C\$6))-DATE(B3,1,1))/365.25))
Re-posted here: https://www.mrexcel.com/forum/excel-questions/1050407-iferror-backwards-compatible.html

Please do not post the same question in multiple places, per rule #12 here: https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html
You are usually better off posting the question to a new thread instead of posting to an old existing thread, especially since many members from the old threads are around much anymore.

#### CMMilne

##### New Member
Thank you, I'm sorry. I couldn't post to a new thread originally, so I posted here and then I could. I forgot to delete this. Again, I'm sorry.

Replies
11
Views
166
Replies
12
Views
123
Replies
1
Views
206
Replies
4
Views
452
Replies
0
Views
157