Finding the top two in a multi page workbook

[rmwarde]

How do I find the top two numbers in a multipage workbook and link it to
another cell. Example

workbook page 1 workbook page 2
student1 500 student9 150
student2 600 student7 1400
student3 450 student6 300

I need it to find the top two numbers of the two workbook pages and and list
the students name.I used the function below, but can't figure out how to use
multiple classes and still get the name to show not the numbers itself.

=INDEX('PM Kinder'!A5:A19,MATCH(MAX('PM Kinder'!C5:C19 ),'PM
Kinder'!C5:C19,0))

Thanks

 

[Leith Ross]

Hello rmwarde,

Use this formula to return the student's name. The formula looks for
largest value in the range and then returns the student's name in the
cell immediately to the left. Change the ranges and worksheet name
accordingly.

=OFFSET('PM Kinder'!A5, MATCH(MAX('PM Kinder'!A5:A19), 'PM
Kinder'!A5:A19,0) - 1, 1, -1)

Sincerely,
Leith Ross

 

[rmwarde]

Okay I changed the ranges, but it comes up with 0

 

[Leith Ross]

Hello rmwarde,

You got the post with the typo. I corrected it shortly after it was
posted. The formula should be...

=OFFSET('PM Kinder'!A5, MATCH(MAX('PM Kinder'!A5:A19), 'PM
Kinder'!A5:A19,0) - 1, -1, 1, 1)

My apologies,
Leith Ross

 

[rmwarde]

Okay, I got that and thankyou! Now how to I use this to have two
worksheets.. I need to find the max of PM Kinder and Am Kinder. (I don't
want the max of each class, but the max of the two classes if I combined
them) Lets say the cell range is the same for both sheets. I found that I
could do this for two pages without the Index, but when I put the Inder in it
just errors out. Am I asking the impossible?

 

[rmwarde]

Sorry I ment OFFSET, not INDEX