SOLVED

Return the column next to the top 3 values

%3CLINGO-SUB%20id%3D%22lingo-sub-2842085%22%20slang%3D%22en-US%22%3EReturn%20the%20column%20next%20to%20the%20top%203%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2842085%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3EI%20would%20like%20to%20return%20the%20name%20of%20a%20column%2C%20if%20it%20is%20in%20the%20top%20three.%20So%20for%20example%3A%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22128%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2280.5px%22%20height%3D%2219%22%3EBest%20Dept%3C%2FTD%3E%3CTD%20width%3D%2246.5px%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.5px%22%20height%3D%2219%22%3EGood%20Dept%3C%2FTD%3E%3CTD%20width%3D%2246.5px%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.5px%22%20height%3D%2219%22%3EOK%20Dept%3C%2FTD%3E%3CTD%20width%3D%2246.5px%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.5px%22%20height%3D%2219%22%3EMeh%20Dept%3C%2FTD%3E%3CTD%20width%3D%2246.5px%22%3E7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.5px%22%20height%3D%2219%22%3EBad%20Dept%3C%2FTD%3E%3CTD%20width%3D%2246.5px%22%3E6%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2280.5px%22%20height%3D%2219%22%3EAwful%20Dept%3C%2FTD%3E%3CTD%20width%3D%2246.5px%22%3E5%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20the%20names%20of%20the%20top%203%20departments%20(so%20in%20this%20case%2C%20best%2C%20good%2C%20and%20OK)%20against%20the%20metric.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20see%20how%20to%20use%20LARGE%20to%20identify%20what%20the%20top%203%20numbers%20are%2C%20but%20I%20can't%20work%20out%20how%20to%20take%20that%20reference%2C%20and%20move%20one%20column%20to%20the%20left%2C%20to%20give%20the%20name.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20MATCH%20or%20VLOOKUP%20doesn't%20work%2C%20because%20the%20metric%20isn't%20always%20unique%20-%20in%20this%20case%20good%20%26amp%3B%20OK%20both%20have%20a%20score%20of%208%2C%20so%20if%20I%20use%20MATCH%20I%20only%20get%20Good%20listed.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20using%20CELL%20and%20OFFSET%20but%20both%20give%20me%20errors%2C%20and%20I%20can't%20seem%20to%20fix%20them.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2842085%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2842252%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20the%20column%20next%20to%20the%20top%203%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2842252%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1184434%22%20target%3D%22_blank%22%3E%40LucyK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20Microsoft%20365%20or%20Office%202021%2C%20enter%20the%20following%20formula%20in%20a%20cell.%20It%20will%20spill%20to%20three%20cells%20automatically.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DFILTER(A1%3AA6%2C%20B1%3AB6%26gt%3B%3DLARGE(B1%3AB6%2C%203))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThe%20following%20will%20work%20in%20any%20version.%20Enter%20the%20following%20formula%20in%20F2%20and%20confirm%20with%20Ctrl%2BShift%2BEnter%2C%20then%20fill%20down%20to%20F4.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDEX(%24A%242%3A%24A%247%2CMATCH(1%2C(%24B%242%3A%24B%247%3DLARGE(%24B%242%3A%24B%247%2CROW()-1))*(COUNTIF(%24F%241%3A%24F1%2C%24A%242%3A%24A%247)%3D%200)%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ESee%20the%20attached%20sample%20workbook.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2842265%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20the%20column%20next%20to%20the%20top%203%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2842265%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1184434%22%20target%3D%22_blank%22%3E%40LucyK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20new%20FILTER%20function%20will%20work%20for%20this%20(it%20does%20require%20the%20newest%20version%20of%20Excel%2C%20however).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20created%20an%20example%20for%20you%20in%20the%20attached.%20You%20pick%20the%20number%20in%20the%20yellow%20cell%20(i.e.%2C%20the%20top%201%2C%202%2C%203%2C%20etc)%2C%20LARGE%20will%20identify%20the%20metric%20associated%2C%20and%20FILTER%20will%20pick%20all%20of%20the%20departments%20that%20meet%20or%20exceed%20that%20metric.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20to%20make%20it%20fully%20dynamic%2C%20I%20created%20a%20Table%20(%22Table2%22%20in%20the%20formulas)%20with%20%22Names%22%20and%20%22Ranks%22%20as%20the%20headers%20for%20the%20two%20columns%20(you'll%20see%20those%20referred%20to%20in%20the%20formulas%20below).%20Creating%20this%20as%20a%20table%20allows%20you%20to%20add%20rows%20indefinitely%20and%20still%20enable%20the%20formulas%20to%20work.%20And%20I%20named%20the%20yellow%20cell%20%22ID_top%22%2C%20which%20you'll%20also%20see%20in%20the%20formula%20referring%20to%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DLARGE(Table2%5BRanks%5D%2CID_top)%20%26nbsp%3B%20%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DFILTER(Table2%5BNames%5D%2CTable2%5BRanks%5D%26gt%3B%3DE5)%3C%2FSTRONG%3E%20%26nbsp%3B%20E5%20is%20the%20cell%20where%20the%20boundary%20rank%20is%20displayed%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20you%20notice%2C%20too%2C%20that%20the%20scores%20do%20not%20need%20to%20be%20sorted%20in%20order.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1634135783169.png%22%20style%3D%22width%3A%20693px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F317077i19EDFD3599E5A81B%2Fimage-dimensions%2F693x246%3Fv%3Dv2%22%20width%3D%22693%22%20height%3D%22246%22%20role%3D%22button%22%20title%3D%22mathetes_0-1634135783169.png%22%20alt%3D%22mathetes_0-1634135783169.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2842491%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20the%20column%20next%20to%20the%20top%203%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2842491%22%20slang%3D%22en-US%22%3EThank%20you!%3C%2FLINGO-BODY%3E
New Contributor

Hi!

I would like to return the name of a column, if it is in the top three. So for example:

Best Dept10
Good Dept8
OK Dept8
Meh Dept7
Bad Dept6
Awful Dept5

 

I want the names of the top 3 departments (so in this case, best, good, and OK) against the metric. 

 

I can see how to use LARGE to identify what the top 3 numbers are, but I can't work out how to take that reference, and move one column to the left, to give the name. 

 

Using MATCH or VLOOKUP doesn't work, because the metric isn't always unique - in this case good & OK both have a score of 8, so if I use MATCH I only get Good listed. 

 

I've tried using CELL and OFFSET but both give me errors, and I can't seem to fix them. 

 

Any help is appreciated!

3 Replies
best response confirmed by LucyK (New Contributor)
Solution

@LucyK 

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.

 

@LucyK 

 

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.

 

mathetes_0-1634135783169.png

 

Thank you!