Help with lookup function
 

I have two worksheets in the same workbook. Sheet one includes data similar to:

Person Average
Colin 72.15
Scott 66.21
John 58.95


In Sheet two I have a function that lets me know what the maximum average
is. My question is, I also want to know what person has the maximum average.
Example:

Max. Average = 72.15
Person = ????

This is just a short example I have 200 people on my list.

Thanks,
Scott

Assume your names are in column A and your average numbers are in column B:

=INDEX(A1:A300,MATCH(MAX(B1:B300),B1:B300,0))


--
Regards,
Dave


"Scott" wrote:

I have two worksheets in the same workbook. Sheet one includes data similar to:

Person Average
Colin 72.15
Scott 66.21
John 58.95


In Sheet two I have a function that lets me know what the maximum average
is. My question is, I also want to know what person has the maximum average.
Example:

Max. Average = 72.15
Person = ????

This is just a short example I have 200 people on my list.

Thanks,
Scott

Assuming your listing is in cells A2:A201

=INDEX('Sheet1'!A2:A201,MATCH(MAX('Sheet1'!B2:B201 ),'Sheet1'!B2:B201))

That will find the FIRST occurrence of the maximum average.



"Scott" wrote:

I have two worksheets in the same workbook. Sheet one includes data similar to:

Person Average
Colin 72.15
Scott 66.21
John 58.95


In Sheet two I have a function that lets me know what the maximum average
is. My question is, I also want to know what person has the maximum average.
Example:

Max. Average = 72.15
Person = ????

This is just a short example I have 200 people on my list.

Thanks,
Scott

Have a look at the following link...

http://groups.google.ca/group/micros...eet.functions/
browse_frm/thread/e74b954662eae4b6/9a629a6291e37589?tvc=1&q=Excel+maximum
+value+and+associated+value&hl=en#9a629a6291e37589

Hope this helps!

In article ,
"Scott" wrote:

I have two worksheets in the same workbook. Sheet one includes data similar
to:

Person Average
Colin 72.15
Scott 66.21
John 58.95


In Sheet two I have a function that lets me know what the maximum average
is. My question is, I also want to know what person has the maximum average.
Example:

Max. Average = 72.15
Person = ????

This is just a short example I have 200 people on my list.

Thanks,
Scott

Dave,
Worked like a charm.

Thank you!

Scott




"David Billigmeier" wrote:

Assume your names are in column A and your average numbers are in column B:

=INDEX(A1:A300,MATCH(MAX(B1:B300),B1:B300,0))


--
Regards,
Dave


"Scott" wrote:

I have two worksheets in the same workbook. Sheet one includes data similar to:

Person Average
Colin 72.15
Scott 66.21
John 58.95


In Sheet two I have a function that lets me know what the maximum average
is. My question is, I also want to know what person has the maximum average.
Example:

Max. Average = 72.15
Person = ????

This is just a short example I have 200 people on my list.

Thanks,
Scott