Help with VLOOKUP and MAX for a Table

[jord9977]

I have an excel assignment that I am stuck on the last question. The questions is as follows:

"Make cells B7:M21 a range called “Data”. Using this range, write a function that
displays the names of the club’s most extravagant and frugal members in cells I28 and I29.
This formula may be somewhat tricky…. If you cannot do this simply use functions to
identify the highest and lowest monthly payments instead."

I have named the range and I have been trying to use a combination of VLOOKUP and the MAX function to return the Last Name to I28 and I29, but I just can't get it to work.

Thank you so much!

A3-1402-W14-Q1.xlsx

[Pete_UK]

Do you know about INDEX and MATCH functions?

Pete

[jord9977]

Not really, is this what I shall use?

[Pete_UK]

It's what I would use if I had that problem to solve.

Essentially, you want to:

a. find the highest value (as with your MAX function)
b. find the row where that occurs in column I
c. get the name from column B that appears on the row given by b.

It's a bit like a reverse lookup. Look at those functions in XL Help and try to figure out how you can use them to get you from a to b and then to c.

Hope this helps.

Pete