# Hopefully, a simple Excel query
**URL:** https://boards.straightdope.com/t/hopefully-a-simple-excel-query/454771
**Category:** Factual Questions
**Created:** [June 30, 2008, 2:15pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-query/454771 "2008-06-30T14:15:33Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@Quartz](https://boards.straightdope.com/u/Quartz)
#### Post date: [June 30, 2008, 2:15pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-query/454771/1 "2008-06-30T14:15:33Z")
I need to search a range (1 or 2 columns, either column M or M & O) for the maximum value (easy enough using MAX), then return the corresponding value from another column (column A). Field names are not unique. There are multiple sets of the same columns horizontally, but column A is unique.
Example:
```auto
Score Score Total Score Score Total
Bloggs 10 10 20 20 10 30
Smith 15 15 30 15 18 33
Adams 15 10 25 20 15 35
```
So if I search on the first total, the max I get is 30, so it would return Smith, and when I search on the second total, the max is 35 and it would return Adams.
How do I do this? I’m sure I’m missing something obvious.
---
### Author:  [@Daylate](https://boards.straightdope.com/u/Daylate)
#### Post date: [June 30, 2008, 3:35pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-query/454771/2 "2008-06-30T15:35:09Z")
Search the help section for “VLOOKUP”. I use it for almost exactly the case you have, selecting hourly rates for employees based on changing productivity. Works like a charm
---
### Author:  [@Quartz](https://boards.straightdope.com/u/Quartz)
#### Post date: [June 30, 2008, 5:00pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-query/454771/3 "2008-06-30T17:00:38Z")
I’ve tried VLOOKUP and HLOOKUP and MATCH and I’m obviously getting it very wrong.
---
### Author:  [@Long\_Time\_Lurker](https://boards.straightdope.com/u/Long_Time_Lurker)
#### Post date: [June 30, 2008, 9:06pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-query/454771/4 "2008-06-30T21:06:47Z")
VLOOKUP uses the first column and compares with subsequent columns. If you put the names in the column after the total, like this:
```auto
Score Score Total Name
Bloggs 10 10 20 Bloggs
Smith 15 15 30 Smith
Adams 15 10 25 Adams
```
Then this will give you the proper result (assuming you started at the top left cell, i.e. D2 - D4 is an array of the totals column):
```auto
=VLOOKUP(MAX(D2:D4),D2:E4,2)
```
There may be a way to work with what you have, but this will work.
---
### Author:  [@Quartz](https://boards.straightdope.com/u/Quartz)
#### Post date: [July 1, 2008, 4:03pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-query/454771/5 "2008-07-01T16:03:48Z")
That did the trick, but what a palaver! Why does it have to be the first column?
---
### Author:  [@BubbaDog](https://boards.straightdope.com/u/BubbaDog)
#### Post date: [July 1, 2008, 7:23pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-query/454771/6 "2008-07-01T19:23:13Z")
If you don’t want to move or duplicate the name column you could combine an “indirect” function with a “match” function.
Assuming the word “Bloggs” is in cell “A2” in your original code box in the op your formula would be :
=INDIRECT(“A” & MATCH(MAX(D2: D4),D2: D4)+1)
And will return the result “Smith”
The indirect statement uses the “A” to specify the column and the results of match to find the row of the table.
I use a variation of this anytime I need to identify a value to the left of the search field or anytime I want to return some matched indexed value in another table.
Edit: There is a space between the : and the D above D2: D4 to avoid the smiley code:D
---
### Author:  [@Quartz](https://boards.straightdope.com/u/Quartz)
#### Post date: [July 1, 2008, 9:28pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-query/454771/7 "2008-07-01T21:28:47Z")
[QUOTE=BubbaDog]
If you don’t want to move or duplicate the name column you could combine an “indirect” function with a “match” function…
=INDIRECT(“A” & MATCH(MAX(D2: D4),D2: D4)+1)
[/QUOTE]
Excellent - I just needed to tweak it to for the exact match qualifier bolded below:
=INDIRECT(“A” & MATCH(MAX(D2: D4),D2: D4\*\*,0\*\*)+1)
---
### Author:  [@Magiver](https://boards.straightdope.com/u/Magiver)
#### Post date: [July 1, 2008, 10:18pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-query/454771/8 "2008-07-01T22:18:30Z")
[QUOTE=BubbaDog]
If you don’t want to move or duplicate the name column you could combine an “indirect” function with a “match” function.
Assuming the word “Bloggs” is in cell “A2” in your original code box in the op your formula would be :
=INDIRECT(“A” & MATCH(MAX(D2: D4),D2: D4)+1)
.
And will return the result “Smith”
The indirect statement uses the “A” to specify the column and the results of match to find the row of the table.
I use a variation of this anytime I need to identify a value to the left of the search field or anytime I want to return some matched indexed value in another table.
Edit: There is a space between the : and the D above D2: D4 to avoid the smiley code:D
[/QUOTE]
I was going to ask what the “+1” does but I see that it adjusts the default to the correct row.
I’ve been using Vlookup (and max) in conjunction with a hidden column to look up the last entry (the hidden column looks for a number and adds 1 to the number above it). Is there a way to find the last number listed in a column directly?
---
### Author:  [@BubbaDog](https://boards.straightdope.com/u/BubbaDog)
#### Post date: [July 2, 2008, 12:44pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-query/454771/9 "2008-07-02T12:44:59Z")
[QUOTE=Magiver]
I was going to ask what the “+1” does but I see that it adjusts the default to the correct row.
I’ve been using Vlookup (and max) in conjunction with a hidden column to look up the last entry (the hidden column looks for a number and adds 1 to the number above it). Is there a way to find the last number listed in a column directly?
[/QUOTE]
I’m not sure about what you are asking and whether the tool is in all versions of excel (I use 2007)
If there is a value in each row up to the last (bottom) then you could combine an indirect statement as above and read the value using one of the “count” functions (Counta returns the number of non-blank cells in the arguments range).
So if you had a table that was routinely appended and had its values starting on row five and there was always a value in say column d then you could "point " to that cell with:
Indirect(“D”& Counta(D5: D900000)+5)
again the “5” is the offset to help position the row.
My apologies if this function isn’t available in earlier Excel versions.
I guess even if there wasn’t always a value in column “D” you could use any other column that never had a nonblank in a record.
---
### Author:  [@Magiver](https://boards.straightdope.com/u/Magiver)
#### Post date: [July 2, 2008, 4:12pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-query/454771/10 "2008-07-02T16:12:26Z")
[QUOTE=BubbaDog]
I’m not sure about what you are asking and whether the tool is in all versions of excel (I use 2007)
If there is a value in each row up to the last (bottom) then you could combine an indirect statement as above and read the value using one of the “count” functions (Counta returns the number of non-blank cells in the arguments range).
So if you had a table that was routinely appended and had its values starting on row five and there was always a value in say column d then you could "point " to that cell with:
Indirect(“D”& Counta(D5: D900000)+5)
again the “5” is the offset to help position the row.
My apologies if this function isn’t available in earlier Excel versions.
I guess even if there wasn’t always a value in column “D” you could use any other column that never had a nonblank in a record.
[/QUOTE]
cool, thanks. I never get over the power of the internet to reach out to people.