Excel Top 5 Formula Explanation

Question

This formula below works wonderfully!

But I am having some trouble wrapping my head around what is going on.

Can somebody explain what is happening here step by step.

I have a good knowledge of formulae and VBA, I have a basic knowledge of array formulae but this has puzzled me.

Evaluating the formula in excel really didn't help.

=INDEX($B$2:$B$11,MATCH(1,INDEX(($B$2:$B$11=LARGE($B$2:$B$11,ROWS(F$1:F1)))*(COUNTIF(F$1:F1,$A$2:$A$11)=0),),0))

Answer 1

As generally with formulas start inside:

• ROWS(F$1:F1) is same as ROW()-1, so it's simply the rank
• LARGE($B$2:$B$11,ROWS(..)) returns the score of actual rank.
  this formula is composed to work using the same formula in both columns. You could leave only this in second column for the same result
• $B$2:$B$11=LARGE(...) results an array with true where score in column B equals the actual one
• COUNTIF(F$1:F1,$A$2:$A$11)=0.
  This doesn't make sense in this column, just an array of true.
  In column E, however, first parameter will be E$1:E1, yielding 0 for all rows where name isn't yet listed in column E. =0 converts it to a list of true for values not yet listed and false for names already listed.
• INDEX((..)*(),) calculates intersect of previous two arrays (score as required by actual rank AND name not yet listed), resulting an array
• MATCH(1,INDEX(..),0) returns the index of first item fulfilling criteria
• INDEX($B$2:$B$B11,MATCH(..)) returns the next name / score