Hopefully, a simple Excel query

Factual Questions
1

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:



               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.

2

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

3

I’ve tried VLOOKUP and HLOOKUP and MATCH and I’m obviously getting it very wrong.

4

VLOOKUP uses the first column and compares with subsequent columns. If you put the names in the column after the total, like this:



               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):



=VLOOKUP(MAX(D2:D4),D2:E4,2)

There may be a way to work with what you have, but this will work.

5

That did the trick, but what a palaver! Why does it have to be the first column?

6

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

7

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)

8

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?

9

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.

10

cool, thanks. I never get over the power of the internet to reach out to people.