What Function
 

I have a table of cells:

A B C D E
1/1/07 5
1/15/07 6
2/1/07 3
(empty) 2
(empty)
(empty)
(empty)
etc.
What formula do I use to look up the highest value in column A and retrieve
the value from THE NEXT ROW in column E. (I need to retrieve the 2)

What Function
 

=VLOOKUP(MAX(A1:A100),A1:E100,5,0)

adjust your range to suit


"wsturdev" wrote:

I have a table of cells:

A B C D E
1/1/07 5
1/15/07 6
2/1/07 3
(empty) 2
(empty)
(empty)
(empty)
etc.
What formula do I use to look up the highest value in column A and retrieve
the value from THE NEXT ROW in column E. (I need to retrieve the 2)

What Function
 

Try something like this:

With
your posted example data in A1:E10

This formula finds the max value in Col_A and returns the corresponding
value from Col_E that is 1 row down
F1: =INDEX(E1:E10,MATCH(MAX(A1:A10),A1:A10,0)+1)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"wsturdev" wrote:

I have a table of cells:

A B C D E
1/1/07 5
1/15/07 6
2/1/07 3
(empty) 2
(empty)
(empty)
(empty)
etc.
What formula do I use to look up the highest value in column A and retrieve
the value from THE NEXT ROW in column E. (I need to retrieve the 2)

What Function
 

Your suggested formula pulls back the 3 from column E of the same row as the
highest date value in column A.

Ron Corderre's post gave me the correct formula.



"Teethless mama" wrote:

=VLOOKUP(MAX(A1:A100),A1:E100,5,0)

adjust your range to suit


"wsturdev" wrote:

I have a table of cells:

A B C D E
1/1/07 5
1/15/07 6
2/1/07 3
(empty) 2
(empty)
(empty)
(empty)
etc.
What formula do I use to look up the highest value in column A and retrieve
the value from THE NEXT ROW in column E. (I need to retrieve the 2)

What Function
 

Perhaps

=INDEX(E2:E101,MATCH(MAX(A1:A100),A1:A100,0))

One alternative is:

=OFFSET(E1,MATCH(MAX(A1:A100),A1:A100,0)+1,0)

Scott

wsturdev wrote:
Your suggested formula pulls back the 3 from column E of the same row as the
highest date value in column A.

Ron Corderre's post gave me the correct formula.



"Teethless mama" wrote:

=VLOOKUP(MAX(A1:A100),A1:E100,5,0)

adjust your range to suit


"wsturdev" wrote:

I have a table of cells:

A B C D E
1/1/07 5
1/15/07 6
2/1/07 3
(empty) 2
(empty)
(empty)
(empty)
etc.
What formula do I use to look up the highest value in column A and retrieve
the value from THE NEXT ROW in column E. (I need to retrieve the 2)