MS Excel - is there a way to do this?

Factual Questions
1

Sorry for the vague title…I couldn’t think of a way to sum up the question in a few words.

Let’s say that in Excel, I have two columns as shown:



Homer   3
Marge   4
Bart    2
Lisa    6
Maggie  5

I want another cell in the sheet to display the name of the person with the biggest number. Using the MAX function will only return the greatest value in the column, and the resulting cell would read “6”. Instead, I want to have a cell that says “Lisa”.

If I could just get a function to return the cell reference of the greatest value, instead of the value itself, I could use OFFSET to cause it to return the row heading. But I don’t know anything that does this. Or maybe that’s not how I want to do this. Is there a better way?

2

It could be done with an if then formula but would be a long ass one. how many entries would you have?

3

Are you commited to using Excel? I could try to come up with some very convoluted way to do this in Excel but MS Access is really the tool for this type of thing. Accomplishing this task in Access is pretty trivial.

4

Let’s just say it would be most convenient to use Excel. If it proves to be mostly impossible, I’ll try with Access.

5

You need the LOOKUP function.

If your sheet starts in cell A1, the formula you’re looking for is:

LOOKUP(MAX(B1:B5), B1:B5, A1:A5)

6

Well, I’ll be damned.

That does it! Thanks so much!

7

Lookup will not work. You need the values in order for LOOKUP to work.

From help:

8

Well, I just figured that out, as well. Weird thing is, it only faisl some of the time when the vector is out of order.

But I can use the MATCH function, and give it *match_type * 0, so that it will use any order. Using the returned position in the array and the OFFSET function, I can reference the right cell.

9

I came up with this, for column A having the text you want, and column B having the numbers, using INDIRECT and MATCH
=INDIRECT(ADDRESS(MATCH(MAX(B1:B7),B1:B7,0),1))

10

I like index better since it shows the range more explicitely

= index(a1:a7,match(max(b1:b7),b1:b7,false),0)

Dorfl