INDEX, MATCH & LARGE trouble with duplicate values

[paulstuartbullock]

Hi, I'm working on a student tracking sheet where students have various grades in different subjects. I'm using 'LARGE' to identify their best grades within a selection of subjects, but I need a way of pulling the column reference to accompany a given cell and locate this in the adjacent cell.

First group of subject titles currently B7:I7
First group of grades B8:I8
Second group of subjects in Q:AV, including 4th-7th highest from first group.

Currently using:
K8=LARGE(B8:G8,1) and similar for the second and third highest values.
J8=INDEX($B$7:$I$7,1,MATCH(K8,$B$8:$I$8,0)) to pull the column titles (Subject names) out.

That is, if a student has five grades say in Maths (7), English (5), French (4), Geography (9) and History (8), and I wish to identify not only the top 3 grades, but also the subjects of those grades and have them reported elsewhere: 9, Geography, 8 History, 7 Maths. It delivers this.

There is a small catch here, I am in the position where I need to take the first three scores and corresponding subjects from one grouping, and the first three from another grouping (Which includes 4th highest onwards from the first group).

I ultimately need these six scores, plus the corresponding subjects to be output somewhere.

The example here:
Science, Double Sci, Triple Sci, Geog, Hist, French
4, 6, 2, 3, 7, 3
has several subjects with an equal score (Perfectly likely), but my formulae only pick up the first value they find and output this twice when this happens,

S8=LARGE(B8:I8, 4)
R8=INDEX($B$7:$I$7,1,MATCH(S8,$B$8:$I$8,0))

S8 returns "Geog", but the same set up for 5th largest also returns "Geog" rather than "French".

How can I get it to distinguish between these duplicate values, take the first one as a first choice value and then 'see' the second duplicate value? Also a little unsure if my use of LARGE 4/5/6/7 is the best solution to identify the lower scores in the first group.
All suggestions greatly appreciated.

 

[jarjarbingie]

try this for the subject names:

=OFFSET($A$7,0,SMALL(IF($B8:$G8=K8,COLUMN($B8:$G8),""),COUNTIF($I8:I8,K8)+1)-1) press CTRL+SHIFT+ENTER

Hi, I'm working on a student tracking sheet where students have various grades in different subjects. I'm using 'LARGE' to identify their best grades within a selection of subjects, but I need a way of pulling the column reference to accompany a given cell and locate this in the adjacent cell.

First group of subject titles currently B7:I7
First group of grades B8:I8
Second group of subjects in Q:AV, including 4th-7th highest from first group.

Currently using:
K8=LARGE(B8:G8,1) and similar for the second and third highest values.
J8=INDEX($B$7:$I$7,1,MATCH(K8,$B$8:$I$8,0)) to pull the column titles (Subject names) out.

That is, if a student has five grades say in Maths (7), English (5), French (4), Geography (9) and History (8), and I wish to identify not only the top 3 grades, but also the subjects of those grades and have them reported elsewhere: 9, Geography, 8 History, 7 Maths. It delivers this.

There is a small catch here, I am in the position where I need to take the first three scores and corresponding subjects from one grouping, and the first three from another grouping (Which includes 4th highest onwards from the first group).

I ultimately need these six scores, plus the corresponding subjects to be output somewhere.

The example here:
Science, Double Sci, Triple Sci, Geog, Hist, French
4, 6, 2, 3, 7, 3
has several subjects with an equal score (Perfectly likely), but my formulae only pick up the first value they find and output this twice when this happens,

S8=LARGE(B8:I8, 4)
R8=INDEX($B$7:$I$7,1,MATCH(S8,$B$8:$I$8,0))

S8 returns "Geog", but the same set up for 5th largest also returns "Geog" rather than "French".

How can I get it to distinguish between these duplicate values, take the first one as a first choice value and then 'see' the second duplicate value? Also a little unsure if my use of LARGE 4/5/6/7 is the best solution to identify the lower scores in the first group.
All suggestions greatly appreciated.