Vlookup Small from a table containing a certain value

[emde84]

Hi,

Here is what I have so far.

I have a list of 100 students from grade 7 to 10 which I have ranked based on their attendance %.

What I am wanting is a list of top 15 attenders based on what grade they are in. lets say grade 7

My thoughts would be to vlookup small 1 through to 15 (from the rank coloumn) and if the grade column = 7 return the name.

I'm not sure how to do this, please help

 

[Aladin Akyurek]

Hi,

Here is what I have so far.

I have a list of 100 students from grade 7 to 10 which I have ranked based on their attendance %.

What I am wanting is a list of top 15 attenders based on what grade they are in. lets say grade 7

My thoughts would be to vlookup small 1 through to 15 (from the rank coloumn) and if the grade column = 7 return the name.

I'm not sure how to do this, please help

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]stud[/TD]
[TD]grade[/TD]
[TD]

attendance %​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]top score(s)[/TD]
[TD]top attender(s)[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]dawn[/TD]
[TD]

7​

[/TD]
[TD]

0.75​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

0.9​

[/TD]
[TD]brian[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]damon[/TD]
[TD]

8​

[/TD]
[TD]

0.85​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

0.9​

[/TD]
[TD]jon[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]dan[/TD]
[TD]

7​

[/TD]
[TD]

0.7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

0.85​

[/TD]
[TD]damon[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]brian[/TD]
[TD]

10​

[/TD]
[TD]

0.9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

0.85​

[/TD]
[TD]christine[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]christine[/TD]
[TD]

8​

[/TD]
[TD]

0.85​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]emde[/TD]
[TD]

10​

[/TD]
[TD]

0.8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]jon[/TD]
[TD]

9​

[/TD]
[TD]

0.9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


F1: 3 (Change to 15 for you data.)

F2: just enter:

=COUNTIFS(C4:C10,">="&LARGE(C4:C10,F1))

F4, just enter and copy down:

=IF(ROWS($F$4:F4)<=$F$2,LARGE($C$4:$C$10,ROWS($F$4:F4)),"")

G4, control+shift+enter, not just enter, and copy down:

=IF($F4="","",INDEX($A$4:$A$10,SMALL(IF($C$4:$C$10=$F4,ROW($C$4:$C$10)-ROW($C$4)+1),
    COUNTIFS($F$4:F4,F4))))

If need be, implement a look up formula in H4 in order to report the grade of the student.

Note. If so desired, the foregoing can also be done by means of a pivot table.

 

[DILIPandey]

Hi emde84,

So you have three columns 1) Student name 2) Grades 3) Rank (based on attendance %)
and you need to identify top 15 students (based on rank) for grade 7.

Hope I am going correct ELSE post a sample data and mention your expected results. thanks.

Regards,
DILIPandey

 

[emde84]

Column 1 rank
Column 2 grade
Column 3 student
Column 4 %

 

[Aladin Akyurek]

Column 1 rank
Column 2 grade
Column 3 student
Column 4 %

Did you try adapting the set up of post #2 to your own lay-out?

 

[DILIPandey]

HI emde84,

Try the suggestion as given in post #2 or post your sample data. Thanks


Regards,
DILIPandey