Re: Index/Match problem
- From: "T. Valko" <biffinpitt@xxxxxxxxxxx>
- Date: Wed, 25 Apr 2007 16:51:41 -0400
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
"Lisa" <Lisa@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:47FE4DDF-FABB-42B0-B0CC-B32E8DAB407A@xxxxxxxxxxxxxxxx
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?
"Teethless mama" wrote:
AutoFilter >> Select Top 10 and change it Top 3 >> OK
"Lisa" wrote:
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!
.
- Follow-Ups:
- Re: Index/Match problem
- From: Lisa
- Re: Index/Match problem
- Prev by Date: Auto response of one column to another
- Next by Date: Re: Auto response of one column to another
- Previous by thread: Auto response of one column to another
- Next by thread: Re: Index/Match problem
- Index(es):
Relevant Pages
|
