Show value in one column from another worksheet based on match in another column

The title probably isn't the best, but let me explain.

I have prepared an Excel workbook to help our 4-H Photography project coordinator tally scores for photos being judged. It shows the highest average score on each worksheet (one per photo category), and the cells containing the average scores update their fill colors depending the score to help them determine what color ribbon the participant will receive.

So there are 15 worksheets for the categories and an extra worksheet for "Best In Show". (There are also two hidden worksheets at the beginning and end called "First" and "Last" to make it easier for 3D references).

The "Best in Show" worksheet currently shows the highest total score from all worksheets along with the worksheet name where that score appears. What I would like to do is to also pull over the name of the student who that score belongs to so the 4-H coordinator doesn't have to go back and look it up on that worksheet.

I am attaching my workbook (no macros), but each of the 15 categories are laid out the same:

Row 1 contains headers

A2:A21 - Student Names
Column B is blank for spacing purposes
C2:C21 - scores from Judge 1
D2:D21 - scores from Judge 2
E2:E21 - scores from Judge 3
Column F is blank for spacing purposes
G2:G21 - Average score (Column C+ Column D + Column E/3)

So I need to match the high score in Column G with the corresponding Student Name in Column A and bring that over to the "Best in Show" worksheet.

I just cannot seem to figure out how. Any and all help is greatly appreciated -- I'm self-taught in Excel and a lot of what I've accomplished is due to previous answers on this board.

Use this array formula instead:

=INDEX(SheetList,MATCH(1,COUNTIF(INDIRECT("'"&SheetList&"'!G2:G21"),D2),0))

Incidentally, Animals - wildlife is missing from your sheetlist - tracking that down took ages!!!!

Incidentally, on each sheet, K7 can be simplified to: =MAX(G2:G21)

and K6 to: =INDEX(A2:A21,MATCH(K5,G2:G21,0)) and it does not need to be entered as an array.

One way:

Formula:

Please Login or Register  to view this content.

array entered.

Glad to have helped.