Forum Discussion
Solved
Return the column next to the top 3 values
Hi! I would like to return the name of a column, if it is in the top three. So for example: Best Dept 10 Good Dept 8 OK Dept 8 Meh Dept 7 Bad Dept 6 Awful Dept 5 I wa...
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.
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.
- Thank you!
