Finding a Value in First Column of Table with VLOOKUP?

[Guest]

I have many tables which look roughly like the following (Row and Column
numbers are also shown):

B C D E F

21 Sunday 10 25 6 96
22 Monday 21 32 9 12
23 Tuesday 5 1 12 0.001
24 Wednesday 47 1 63 41
25 Thursday 1 8 0.5 7
26 Friday 6 55 11 58
27 Saturday 8 62 32 12

Only the numerical values change in all the tables.

I need a formula that will identify the highest numerical value (which is 96
here) and return the day of the week from column 1 in which it falls (here,
Sunday). I've been trying to use VLOOKUP in conjunction with MAX, but I'm
clearly screwing that up because I keep getting an interesting array of error
messages.

Does anyone have any ideas? VLOOKUP seems designed to do the opposite of
what I want, since I want to always find the value in the first column that
corresponds to a value inside the table. Is there a better way to go? Help!

 

[Bob Phillips]

=INDEX(C:C,SUMPRODUCT(--(D1:H7=MAX(D1:H7))*ROW(D1:H7)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I have many tables which look roughly like the following (Row and Column
numbers are also shown):

B C D E F

21 Sunday 10 25 6 96
22 Monday 21 32 9 12
23 Tuesday 5 1 12 0.001
24 Wednesday 47 1 63 41
25 Thursday 1 8 0.5 7
26 Friday 6 55 11 58
27 Saturday 8 62 32 12

Only the numerical values change in all the tables.

I need a formula that will identify the highest numerical value (which is 96
here) and return the day of the week from column 1 in which it falls (here,
Sunday). I've been trying to use VLOOKUP in conjunction with MAX, but I'm
clearly screwing that up because I keep getting an interesting array of error
messages.

Does anyone have any ideas? VLOOKUP seems designed to do the opposite of
what I want, since I want to always find the value in the first column that
corresponds to a value inside the table. Is there a better way to go?

Help!

 

[Guest]

Thanks! Boy, was I on the wrong track. It's going to take me a bit to figure
out why this works, but you've given me what I need to know. Excellent!

 

[Bob Phillips]

Actually, there is a superfluous -- in there

=INDEX(C:C,SUMPRODUCT((D1:H7=MAX(D1:H7))*(ROW(D1:H7))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)