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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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))))
 
Upvote 0
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."
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top