Excel MATCH Function problem

[andyp161]

Is it possible, when using the MATCH function, to find all matche
rather than only the first match when using '0'

 

[Frank abel]

Hi
and what do you want to do with the result (sum them?)

 

[andyp161]

Hi Frank,

I'm trying to develop a formula that will locate the highest awarde
points in a range and then in the cells to the right of the matche
highest numbers insert bonus points - my problem is that when using th
IF, MATCH, LARGE and CHOOSE functions as below to allocate bonus point
as follows 1st=500, 2nd=250, 3rd=125, 4th=75, if there are say tw
people with equal highest points, the formula will allocate 500 point
to both highest earners but then allocate the 3rd prize to 2n
place...

=IF(ISERROR(MATCH(A1,LARGE($A$1:$A$10,{1,2,3,4}),0)),"",CHOOSE(MATCH(A1,LARGE($A$1:$A$10,{1,2,3,4}),0),500,250,125,75)


Kind regard

 

[Frank Kabel]

Hi
you may try something like the following:
1. Create a list with your top 5 values (using LARGE). e.g.
=LARGE($A$1:$A$10,ROW(1:1))
and copy this down for 5 rows (e.g. in column C)

2. use a combination of RANK and CHOOSE to assign the
bonus points. e.g.
=CHOOSE(RANK(C1,$A$1:$A$10),500,250,125,75)
and copy this down

 

[andyp161]

Thanks Frank. I've tried your suggestion but it appears that th
problem remains that if there are two highest earners on say 1000 each
500 points (1st prize ) will be allocated to both, but then the secon
highest earner on say 999 will be awarded 125 points (3rd prize
instead of 250 points (2nd prize). My intention is that all highes
earners receive 500 points (even 20 people were to have earned the sam
highest figure) and the same for the 2nd, 3rd and 4th

 

[Frank Kabel]

Hi
for this see:
http://www.cpearson.com/excel/rank.htm

with explanation how to solve this kind of issue