Excel MATCH Function problem

  • Thread starter Thread starter andyp161
  • Start date Start date
A

andyp161

Is it possible, when using the MATCH function, to find all matche
rather than only the first match when using '0'
 
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
 
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
 
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
 
Back
Top