# MS Excel - is there a way to do this?
**URL:** https://boards.straightdope.com/t/ms-excel-is-there-a-way-to-do-this/280256
**Category:** Factual Questions
**Created:** [December 17, 2004, 2:40am UTC](https://boards.straightdope.com/t/ms-excel-is-there-a-way-to-do-this/280256 "2004-12-17T02:40:32Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@aerodave](https://boards.straightdope.com/u/aerodave)
#### Post date: [December 17, 2004, 2:40am UTC](https://boards.straightdope.com/t/ms-excel-is-there-a-way-to-do-this/280256/1 "2004-12-17T02:40:32Z")
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:
```auto
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?
---
### Author:  [@Aural\_Sects](https://boards.straightdope.com/u/Aural_Sects)
#### Post date: [December 17, 2004, 3:21am UTC](https://boards.straightdope.com/t/ms-excel-is-there-a-way-to-do-this/280256/2 "2004-12-17T03:21:13Z")
It could be done with an if then formula but would be a long ass one. how many entries would you have?
---
### Author:  [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)
#### Post date: [December 17, 2004, 3:21am UTC](https://boards.straightdope.com/t/ms-excel-is-there-a-way-to-do-this/280256/3 "2004-12-17T03:21:20Z")
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.
---
### Author:  [@aerodave](https://boards.straightdope.com/u/aerodave)
#### Post date: [December 17, 2004, 3:57am UTC](https://boards.straightdope.com/t/ms-excel-is-there-a-way-to-do-this/280256/4 "2004-12-17T03:57:29Z")
Let’s just say it would be most convenient to use Excel. If it proves to be mostly impossible, I’ll try with Access.
---
### Author:  [@Athena](https://boards.straightdope.com/u/Athena)
#### Post date: [December 17, 2004, 3:59am UTC](https://boards.straightdope.com/t/ms-excel-is-there-a-way-to-do-this/280256/5 "2004-12-17T03:59:04Z")
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)
---
### Author:  [@aerodave](https://boards.straightdope.com/u/aerodave)
#### Post date: [December 17, 2004, 4:23am UTC](https://boards.straightdope.com/t/ms-excel-is-there-a-way-to-do-this/280256/6 "2004-12-17T04:23:16Z")
Well, I’ll be damned.
That does it! Thanks so much!
---
### Author:  [@erislover](https://boards.straightdope.com/u/erislover)
#### Post date: [December 17, 2004, 4:30am UTC](https://boards.straightdope.com/t/ms-excel-is-there-a-way-to-do-this/280256/7 "2004-12-17T04:30:47Z")
Lookup will not work. You need the values in order for LOOKUP to work.
From 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.
---
### Author:  [@aerodave](https://boards.straightdope.com/u/aerodave)
#### Post date: [December 17, 2004, 4:41am UTC](https://boards.straightdope.com/t/ms-excel-is-there-a-way-to-do-this/280256/8 "2004-12-17T04:41:57Z")
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.
---
### Author:  [@erislover](https://boards.straightdope.com/u/erislover)
#### Post date: [December 17, 2004, 4:50am UTC](https://boards.straightdope.com/t/ms-excel-is-there-a-way-to-do-this/280256/9 "2004-12-17T04:50:19Z")
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))
---
### Author:  [@dorfl](https://boards.straightdope.com/u/dorfl)
#### Post date: [December 17, 2004, 8:32am UTC](https://boards.straightdope.com/t/ms-excel-is-there-a-way-to-do-this/280256/10 "2004-12-17T08:32:35Z")
> [@erislover](#):
>
> 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))
I like index better since it shows the range more explicitely
= index(a1:a7,match(max(b1:b7),b1:b7,false),0)
Dorfl