Index/Match problem

[Guest]

Hello,

I have a pivot table with the following data:

Apples $1,000
Bananas $1,000
Pears $1,000
Oranges $500
Peaches $450

Using the "LARGE" formula, I am pulling the 3 largest values of $1,000. I
need to match the name to the value, but my formula is duplicating the first
name 3 times so it looks like this:

apples $1,000
apples $1,000
apples $1,000

How do I get the formula to recognize there are 3 different names?

My formula:
=INDEX($C$13:$C$65536,MATCH($E$10,$F$13:$F$65536,0))

Any help is greatly appreciated!! Thanks!

 

[Guest]

AutoFilter >> Select Top 10 and change it Top 3 >> OK

 

[Guest]

one thing I have done in this situation is to add some helper columns with
=B1+row()/1000 copy down
(value selected to insure no overlap depending on number of variables and
minimum incremental difference)
use a second helper column with =rank(C1,C:C,1) copy down

use index(A:A,Match(1,D)
and index(B:B,Match(1,D)
the same for 2, 3 4 etc.
You can use other secondary tie breakers depending on what you want to do.
I have used multiple levels of tie breakers.

You can, of course, make the make the equations more fancy and by changing
order of columns to put the ranking formula in the first column, use
Vlookup() instead of index match

 

[Guest]

Yes, I realize I can filter and sort the data within the table, but I need to
extract the largest values from the table and then the names associated with
them. In my case, because the 3 largest values are all the same, I am getting
only one name repeated 3 times. I don't know how to get it to differentiate
the names - possibly use in IF statement within the formula?

 

[Guest]

I'm thinking you'd have to put in a helper column with something like
=COUNTIF($A$2:A2,A2) (to check row 2).

 

[T. Valko]

It appears that your table is already sorted on the number value.

My formula:
=INDEX($C$13:$C$65536,MATCH($E$10,$F$13:$F$65536,0))

I'm assuming E10 is your LARGE formula that returns 1000. Is your range of
data really all the way to row 65536?

=IF(ROWS($1:1)<=COUNTIF(F$13:F$17,E$10),INDEX(C$13:C$17,MATCH(E$10,F$13:F$17,0)+ROWS($1:1)-1),"")

Copy down until you get blanks.

If you want a top n list then it gets pretty complicated when there are ties
involved.

Biff

 

[Guest]

This works perfectly - THANK YOU SO MUCH!!!

It appears that your table is already sorted on the number value.


I'm assuming E10 is your LARGE formula that returns 1000. Is your range of
data really all the way to row 65536?

=IF(ROWS($1:1)<=COUNTIF(F$13:F$17,E$10),INDEX(C$13:C$17,MATCH(E$10,F$13:F$17,0)+ROWS($1:1)-1),"")

Copy down until you get blanks.

If you want a top n list then it gets pretty complicated when there are ties
involved.

Biff

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

This works perfectly - THANK YOU SO MUCH!!!