INDEX/MATCH/LARGE revistied...

[Derrich R.]

Yesterday, I wrote...

=INDEX($A$1:$A$140,MATCH(LARGE
($Z$1:$Z$140,N),$Z$1:$Z$140,0)) ....
The problem is, in 2 of the 4 lists, I am getting repeat
answers. For example, if I copy down the function above
and replace the "N" with 1, 2, 3, 4, and so on...those
cells are returning the same answer. In one list, it
repeats 8 times, and in the next list, the first result
repeats 5 times.

Can I use 2 columns to determine my result...say, using a
SUMPRODUCT or something to that extend. For example,
return the value ranked 1 in Column Y AND Column Z. The
problem is that my Column Z is limited to a value of "30"
(with a MIN function). There are 8 "30s" in one list and
5 "30s" in another before it actually begins trickling
down. So, I thought maybe I could place 2 parameters on
the result rather than only one since there are a few
ranked with the same value.

Thoughts? Need more info? Thanks, again.

 

[Jason Morin]

Try this. In AA1, put this formula and copy down:

=IF(COUNTIF($Z$1:Z1,Z1)>1,"",Z1)

and then change your formula to:

=INDEX($A$1:$A$140,MATCH(LARGE
($AA$1:$AA$140,N),$AA$1:$AA$140,0))

What this will do is ignore any duplicates you have in
Z1:Z140. For instance, if you had the array {5,3,5,8}, the
formula in AA will give you {5,3,"",8}. Note that 5 is the
second largest, and it will choose the first 5, and the
INDEX f(x) will return the first cell (A1) in this case.

HTH
Jason
Atlanta, GA


HTH
Jason
Atlanta, GA