how can we get unique values in match function for same match key.

X

Xcel

i picked top10 values with the help of the large formula from an array of
values, on that i wrote match function, with the help of the match function
values i wrote index for getting respective name for the value.its working
propely when the values are not repeated if any value repeated large working
properly but match was giving the first match value only,for that i need
individual match values. pls anyone help me.
1 san
10 raj
9 cami
12 jhon

Regards,
Santhosh.

X

Xcel

hi valko thanks for your help,
but i didn't solve my problem exactly, according to you solution the series
must be in descending order. i didn't have the series in any order
i was thankful to you forever.

T

X

T

T. Valko

You're welcome. Thanks for the feedback!

X

Xcel

volko small favour for me, can we do this with out row and rows functions.

T

T. Valko

Why do you want to do that?

Basically, the answer would be no but if you explain why there might be some
other formula that might do what you want.

X

Xcel

i am using some tool it won't supports the row function,
thats why i am asking.if i am troubling you extreamly sorry.

T

T. Valko

Ok, without using ROW or ROWS...

Assume:

A2:A16 = named range = Sales
B2:B16 = named range = Names
C2:C16 = named range = Ranks

Enter this formula in C2 and copy down to C16:

=RANK(A2,sales)+COUNTIF(A\$2:A2,A2)-1

In E2:E11 enter the numbers 1,2,3,4,5,6,7,8,9,10

Enter this formula in F2 and copy down to F11:

=LARGE(sales,E2)

Enter this formula in G2 and copy down to G11:

=INDEX(names,MATCH(SMALL(ranks,E2),ranks,0))

X

Xcel

Thanks Volko, thanks for your help
i already written this one but it not supporting by the tool.
for that reason i am searching for alternatives.

T

T. Valko

That's some "useful" tool you got there!

Good luck!