# Thread: Return date value based on the results of another cell that returns a MAX

1. ## Return date value based on the results of another cell that returns a MAX

I have dates in cells B5 to B9 and number in C5 to C9. In cell K2, I have a formula to show the maximumvalue between C5 to C9 (=MAX(C5:C9).

In cell L2, I would like a formula that displayed the date fromcells B5 to B9, based on the formula in K2 that showed the maximum value.
Example: C7 has the maximumvalue

B5: 05/15/2011
B6: 05/16/2011
B7: 05/17/2011
B8: 05/18/2011
B9: 05/19/2011

C5: 101
C6: 102
C7: 150
C8: 103
C9: 104

Results:

K2 = 150
L2 = 05/17/2011

I would appreciate help in writing the formula for cell L2 to give me the date.

2. Here's one possibility I came up with:
=OFFSET(FirstDate,(MATCH(K2,MaxLookup,0)-1),0)
where:
FirstDate is a range name for B5
MaxLookup is a range name for C5:C9

Hope this helps.

3. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

sbdale (2011-05-15)

4. RetiredGeek,

This is exactly what I was looking for. Thanks.

5. I think this will also work: =index(B5:B9,MATCH(max(C5:C9),C5:C9,0))

6. ## The Following User Says Thank You to kweaver For This Useful Post:

sbdale (2011-05-16)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•