Working out a capped point score using LARGE function

[Doglegs]

Hi there - sorry if this is long winded - hopefully it makes some sense??!!:

i'm trying to work out a formula to find a capped point score for a set of
grades. The capped point score is for the 'best 8' grades. Some of the
grades are worth more than others(i.e. some grades contribute twice the
points of others, some are 1/2 qualifications), so i have converted them into
a 'standardised grade'. The point score is indicated in row 2. The
contribution to the grade threshold is in row 3 (e.g. C2 gives 28*2 points,
and D2 gives 46*0.5 points

A B C D E F G H I J
K L M N O
1 QUALIFICATION POINTS 1 2 3 4 5
2 16 16 34 46 34... 46 34 34 16 16
0.5 2 2
3 1 2 2 0.5 1...

Columns A-E show the standardised point scores, with A3:E3 (at the bottom of
the sheet), displaying the grade threshold indicators.

Columns G-K rank the standardised points in descending order using
"=Large($A2:$E2,G$1)"

My problem is with columns M-O where i am trying to display the
corresponding threshold indicator for each grade. E.g., using:

M2=HLOOKUP((LARGE($A2:$E2,G$1)),$A2:$E$3,2,FALSE)

it will only display the first lookup for cells with the same value. For
instance, there are two standardised point scores of 34, which should be
'Large 2' and 'Large 3'. One is worth 2 grades, the other is worth 1.
However the above formula will only show '2' for either, as it is first in
the list. - I want cell O2 to display '1'

Is there a way around this?

Thanks

 

[Gary''s Student]

Lookups (vlookup or hlookup) retrun only the first instance of the found
item. To return multiple instances see:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

 

[Doglegs]

Thanks - it's a bit trickier as my data is in columns rather than rows - but
i think it's sorted

cheers.

i've ended up with a separate array formula for each large value which looks
like this:

=IF(ISERROR(INDEX(A4O13,DQ4,LARGE(IF($CI4:$DO4=DS4,COLUMN($CI:$DO)),COLUMN($1:$1)))),0,(INDEX(A4O13,DQ4,LARGE(IF($CI4:$DO4=DS4,COLUMN($CI:$DO)),COLUMN($1:$1)))))