Excel Help

[Skanky Burns]

I have a list of stuff in columns A, B and C. Lets pretend these are first name, last name and test score. What I want to do is display below this list the highest score as well as the person's name next to it.

Easy part first: finding the highest value.
=MAX(C2:C4)

The problem I'm having is getting A to display the information in cellA where cell C is the highest and ditto for B. Can it be done and how do I do this?

[Rufus T. Firefly]

Sounds like a simple data sort. Highlight all the data, then sort by column C. Or am I missing something?

[Skanky Burns]

This is similar to what I want it to do.

[Skanky Burns]

I don't want to sort the data at all (they have to be in a certain order not based on C) but just display the highest value down the bottom and the information that goes along with this highest value.

[Fake Boris]

What Rufus said.

If you REAALLY need to use a formula, you could do a MATCH + INDEX combo, but it would be useless.

[Rufus T. Firefly]

Oh, I see. No idea, then. Sorry.

[Kuciwalker]

=INDIRECT(ADDRESS(MATCH(MAX(range),range), namecol))

Where range is the scores, and namecol is, well, the columns with names.

[Fake Boris]

Originally posted by Skanky Burns
I don't want to sort the data at all (they have to be in a certain order not based on C) but just display the highest value down the bottom and the information that goes along with this highest value.

a) use a filter
b) use INDEX + MATCH

basically it should look like this (but you should check the exact syntax in the help): INDEX(lookup_range,row_num,column_number)

Lookup range can be either a fixed argument, or a variable one using the OFFSET function.

ROW_NUM will be found by MATCH(MAX(C2:C4),lookup_range)
column_number will most likely be a constant of your choice, or it could be the COLUMN() function.

[Fake Boris]

Kuci's suggestion works, but it takes longer to calculate

[Kuciwalker]

Ah, yes, index is a much less half-assed way than indirect(address())

Though I doubt calculation time would be significant for anything he's using it for.

[Skanky Burns]

Cheers Kuci, thats done the trick.

EDIT: And OBoris.