Find and return Top n Values

[praetorianprefect]

I used both the Large() and Index/Match functions to find the top 5 largest and its corresponding names. The problem is when the top value are the same for two different names it repeats the name twice. See setup below:

Name Sales
================
Jim 100
Jake 75
Joe 100
Jill 50
Jason 45
John 35

Large function will list both 100 but Index will return Jim twice...any ideas? Thank you.

[NBVC]

Assuming your table is in A1:B7 with headers in A1:B1, and assuming you listed the largest 5 values in C2:C6, then in D2 enter:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copy down.

[Richard Buttrey]

Checkout NBVC's post here earlier this year.

http://www.excelforum.com/excel-work...-function.html

Regards

[NBVC]

Well, at least I am consistent

[praetorianprefect]

Assuming your table is in A1:B7 with headers in A1:B1, and assuming you listed the largest 5 values in C2:C6, then in D2 enter:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copy down.

THANK YOU!!!

Could you email me an explanation? My brain starting to hurt...

[NBVC]

=INDEX($A$2:$A$7,SMALL(IF($B$2:$B$7=C2,ROW($B$2:$B$7)-ROW($B$2)+1),COUNTIF(C$2:C2,C2)))

is like a vlookup for multiple matches... It uses the Index() function which requires you to index the table or column to extract from and it requires the Row number to extract from which is gotten from this part: SMALL(IF($B$2:$B$7=C2,ROW($B$2:$B$7)-ROW($B$2)+1),COUNTIF(C$2:C2,C2))

The Small() function just allows us to step up and extract one match at a time, starting from the first match found. It will extract only if a matching number to C2 is found in range $B$2:$B$7 and then it will return the corresponding row number within the range. The -ROW($B$2)+1 is added to offset the fact you didn't start at row 1...

The last COUNTIF(C$2:C2,C2) is the k factor for the Small() function, which is like a step factor in a For...next loop in VBA. It counts the number of identical items in column C from start to point you are at to determine the "step" value

The CSE confirmation is because it is an array formula and you have to confirm with those keys to make these formulas work.

Then you copy down... The only thing that changes copying down is the COUNTIF(C$2:C2,C2) .. again to determine the step or next matching item.

Hope this clarifies a bit.