| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
| Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
| Date & Time | New Users to Excel | |||
| Conversion | Excel Worksheet Functions | |||
| HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Linear Mode
