Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Function help | Excel Discussion (Misc queries) | |||
Lookup Function Problems | Excel Worksheet Functions | |||
Complicated lookup function | Excel Worksheet Functions | |||
lookup function 1 | Excel Worksheet Functions | |||
How do I use 3 cells to create the string for a lookup function? | Excel Worksheet Functions |