Calculate Highest Number

Topic

New Reply

Column 1 contains a list of names, and column 2 calculates the number of lunches that person attended. I know that I can use the MAX function on column 2 to determine the highest number in the lunches column, but how do I determine the name of the person who matches the MAX number?

I am using Excel 2000/SP-3

Thanks for your help.

Reply | Quote

Replies

The attached example should solve your dilemma. Note that there may be more than one person with the most. Also note that in rows 2:6, columns E:G have the same formulae; Row 1 is unique.

Reply | Quote

The attached example should solve your dilemma. Note that there may be more than one person with the most. Also note that in rows 2:6, columns E:G have the same formulae; Row 1 is unique.

Thank you for your help! I’ll give a try.

Reply | Quote

An easier formula is to use:

=LOOKUP(MAX(B2:B100),B2:B100,A2:A100)

Assuming the list of diners is in range A2:A100. Adjust to fit

Reply | Quote

An easier formula is to use:

=LOOKUP(MAX(B2:B100),B2:B100,A2:A100)

Assuming the list of diners is in range A2:A100. Adjust to fit

I don’t think that approach will work in this instance. Note the following from LOOKUP Help

Important The values in lookup_vector must be placed in ascending order: …,-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.

Reply | Quote