Results 1 to 7 of 7

Thread: Backwards compatibility to 97-03 for IFERROR array
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2014
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Backwards compatibility to 97-03 for IFERROR array

    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.

  2. #2
    Board Regular
    Join Date
    Dec 2009
    Location
    Yorkshire, UK
    Posts
    87
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Backwards compatibility to 97-03 for IFERROR array

    =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))))

  3. #3
    New Member
    Join Date
    Jan 2014
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Backwards compatibility to 97-03 for IFERROR array

    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?

  4. #4
    New Member
    Join Date
    Jan 2014
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Backwards compatibility to 97-03 for IFERROR array

    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))),"")

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,275
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Backwards compatibility to 97-03 for IFERROR array

    Quote Originally Posted by chilisrool View Post
    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.

  6. #6
    New Member
    Join Date
    Jan 2014
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Backwards compatibility to 97-03 for IFERROR array

    Marcelo, that formula also comes up with the same message about levels of nesting. Thanks anyway.

  7. #7
    New Member
    Join Date
    Jan 2014
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Backwards compatibility to 97-03 for IFERROR array

    Can anyone else offer a solution that will work in Excel 97?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •