Forum Discussion

DCL611's avatar
DCL611
Copper Contributor
Sep 13, 2020
Solved

Using Rank Function but with filtering through another criteria as well.

Hello to anyone who can help and thank you in advance, I would like to populate a table that is based on the Rank of 1 criteria, "Potential Return", but then also specifically for another criteria w...
Formulas and Functions
  • mtarler's avatar
    mtarler
    Sep 16, 2020

    DCL611  You are not doing anything wrong, your version of Excel doesn't have dynamic arrays (which means you also don't have some of the new functions that come with it like FILTER(), UNIQUE() and some others).  So going back to old school tricks I think this should work for you:

    =IFERROR(OFFSET(Ranks!$Y$1,AGGREGATE(15,7, ROW(Ranks!$Z:$Z)/(Ranks!$Z:$Z=$AQ8),COUNTIF($AQ$1:$AQ8,$AQ8))-1,0)," ")

    see attached.  If it works for you I hope you like and mark this as best answer 🙂

mtarler's avatar
mtarler
Silver Contributor
Sep 16, 2020

DCL611  You are not doing anything wrong, your version of Excel doesn't have dynamic arrays (which means you also don't have some of the new functions that come with it like FILTER(), UNIQUE() and some others).  So going back to old school tricks I think this should work for you:

=IFERROR(OFFSET(Ranks!$Y$1,AGGREGATE(15,7, ROW(Ranks!$Z:$Z)/(Ranks!$Z:$Z=$AQ8),COUNTIF($AQ$1:$AQ8,$AQ8))-1,0)," ")

see attached.  If it works for you I hope you like and mark this as best answer 🙂

Resources