Backwards compatibility to Excel 97 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. 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.
 

Some videos you may like

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
Joined
Feb 16, 2013
Messages
6,723
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
Joined
Jan 30, 2014
Messages
34
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
Joined
Feb 16, 2013
Messages
6,723
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
Joined
Jan 30, 2014
Messages
34

ADVERTISEMENT

Thanks, the helper column worked for me.
 

CMMilne

New Member
Joined
Apr 4, 2018
Messages
5

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,895
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 4, 2018
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,335
Messages
5,547,336
Members
410,785
Latest member
phillippaige
Top