Finding a Value in First Column of Table with VLOOKUP?

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!

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael Link" <MichaelLink@discussions.microsoft.com> wrote in message
news:25B88089-6AF6-4DFD-A29C-D67E6F092EC1@microsoft.com...
>
> 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!

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" wrote:

> =INDEX(C:C,SUMPRODUCT(--(D1:H7=MAX(D1:H7))*ROW(D1:H7)))
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Michael Link" <MichaelLink@discussions.microsoft.com> wrote in message
> news:25B88089-6AF6-4DFD-A29C-D67E6F092EC1@microsoft.com...
> >
> > 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!
>
>
>

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)

"Michael Link" <MichaelLink@discussions.microsoft.com> wrote in message
news:8F1A4370-EB45-44CA-BA9B-1767081D2E41@microsoft.com...
> 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" wrote:
>
> > =INDEX(C:C,SUMPRODUCT(--(D1:H7=MAX(D1:H7))*ROW(D1:H7)))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Michael Link" <MichaelLink@discussions.microsoft.com> wrote in message
> > news:25B88089-6AF6-4DFD-A29C-D67E6F092EC1@microsoft.com...
> > >
> > > 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!
> >
> >
> >