Forum Discussion
Return the column next to the top 3 values
If you have Microsoft 365 or Office 2021, enter the following formula in a cell. It will spill to three cells automatically.
=FILTER(A2:A7, B2:B7>=LARGE(B2:B7, 3))The following will work in any version. Enter the following formula in F2 and confirm with Ctrl+Shift+Enter, then fill down to F4.
=INDEX($A$2:$A$7,MATCH(1,($B$2:$B$7=LARGE($B$2:$B$7,ROW()-1))*(COUNTIF($F$1:$F1,$A$2:$A$7)= 0),0))See the attached sample workbook.
The new FILTER function will work for this (it does require the newest version of Excel, however).
I've created an example for you in the attached. You pick the number in the yellow cell (i.e., the top 1, 2, 3, etc), LARGE will identify the metric associated, and FILTER will pick all of the departments that meet or exceed that metric.
Just to make it fully dynamic, I created a Table ("Table2" in the formulas) with "Names" and "Ranks" as the headers for the two columns (you'll see those referred to in the formulas below). Creating this as a table allows you to add rows indefinitely and still enable the formulas to work. And I named the yellow cell "ID_top", which you'll also see in the formula referring to it.
=LARGE(Table2[Ranks],ID_top)
=FILTER(Table2[Names],Table2[Ranks]>=E5) E5 is the cell where the boundary rank is displayed
I hope you notice, too, that the scores do not need to be sorted in order.