Showing maximum count text only

Hi,

I have enclosed one file where could you please let me know name of person who has won maximum games by formula only ?


Year Winner in Games
2002 Mark
2003 Tom
2004 Arthur
2005 james
2006 Mark
2007 james
2008 Mark
2009 james
2010 Mark
2011 Mark
2012 Mark
2015 Mark


Thanks
Vijay

=LOOKUP(2,1/(MAX(INDEX(COUNTIFS(B5:B16,B5:B16),0))=INDEX(COUNTIFS(B5:B16,B5:B16),0)),B5:B16)

Please Login or Register  to view this content.

try this formula

with a helpcolumn and a pivot table.

See the attached file.

Originally Posted by JR DHONA

hi..

please check below array formula :
{=LOOKUP(MAX(COUNTIF($B$5:$B$16,$B$5:$B$16)),COUNTIF($B$5:$B$16,$B$5:$B$16),$B$5:$B$16)}

or non array formula
=LOOKUP(MAX(INDEX(COUNTIF($B$5:$B$16,$B$5:$B$16),,)),COUNTIF($B$5:$B$16,$B$5:$B$16),$B$5:$B$16)

Best Regards,
JRD

I think in Lookup formula the values in Lookup vector must be in Ascending Order

Originally Posted by zbor

Another way:

Formula:

Please Login or Register  to view this content.

Comfirmed with ctrl+shift+enter (not just enter)

I think no need to Comfirm with ctrl+shift+enter it is working as same as array formula

Thanks lot but i need second highest person name then !!

Use Pivot Table

Can it be done by formulas .

First:
=INDEX(B5:B16,MODE(IF(B5:B16<>"",MATCH(B5:B16,B5:B16,0)))) as suggested above...

Second:
=INDEX(B5:B16,MODE(IF((B5:B16<>"")*(B5:B16<>INDEX(B5:B16,MODE(IF(B5:B16<>"",MATCH(B5:B16,B5:B16,0))))),MATCH(B5:B16,B5:B16,0))))

Both are array formulae.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

and... (before you ask)...

Third:
=INDEX(B5:B16,MODE(IF(((B5:B16<>"")*(B5:B16<>INDEX(B5:B16,MODE(IF(B5:B16<>"",MATCH(B5:B16,B5:B16,0)))))*(B5:B16<>INDEX(B5:B16,MODE(IF((B5:B16<>"")*(B5:B16<>INDEX(B5:B16,MODE(IF(B5:B16<>"",MATCH(B5:B16,B5:B16,0))))),MATCH(B5:B16,B5:B16,0)))))),MATCH(B5:B16,B5:B16,0))))

or try below array formula Inspired by Glenn kennedy
E5

Please Login or Register  to view this content.

E6

Please Login or Register  to view this content.

Copy E6 and copy towards down

Forget my last effort. Try this (it will also work for fourth place, at which point nflsales' formula seems to fail).
First:
IFERROR(INDEX(B5:B16,MODE(MATCH(B5:B16,B5:B16,0)+{0,0})),"")

The rest (copied down):
IFERROR(INDEX($B$5:$B$16,MODE(IF(COUNTIF($D$5:D5,$B$5:$B$16)=0,MATCH($B$5:$B$16,$B$5:$B$16,0)+{0,0}))),"")

All array formulae.