how do I return tthe location (cell) when using the max function

[Guest]

I keep weekly values of my investments in a row and then search with the max
function. How can I return the date (row 3 of the colum that max value is
in) that the max value occured?

 

[Guest]

Consider using:

=HLOOKUP()

 

[Dave Peterson]

The max for that row?

If yes:

=index($c$3:$l$3,match(max(c4:l4),c4:l4,0))

if your dates were in C3:L3 and your values to check were in C4:L4

 

[Dave Peterson]

Oops. I meant to reply to the OP.

The max for that row?

If yes:

=index($c$3:$l$3,match(max(c4:l4),c4:l4,0))

if your dates were in C3:L3 and your values to check were in C4:L4

 

[Guest]

That's o.k.....I can use it too.
--
Gary's Student
gsnu200709

Oops. I meant to reply to the OP.

 

[Guest]

Thank You
That worked great
If you have time I would like an explanation of the formula.

Oops. I meant to reply to the OP.

 

[Dave Peterson]

=max(c4:l4)
returns the largest value in that range

=match(x,somerow,0)
looks for the exact match (that 0 means exact) in somerow. If it finds an exact
match, then this returns a number (1 for the first cell in somerow, 2 for the
second cell, ...n for the nth cell).

=index(someotherrow,n)
picks out the nth cell in someotherrow

Thank You
That worked great
If you have time I would like an explanation of the formula.