Lookup max date only if there is a value in C

[JenC]

In Column A I have a list of dates. When somebody uses FMLA I will enter the hours used into Column C. I need excel to search for the max date with a value in C and give me the total which will be in in Column E of the same row.

Example:
A B C D E
1/2/2008 FMLA 8 DS 8

1/4/2008 FMLA 1 XEND 9

1/6/2008 FMLA 3 DS 12

[TIPPYS]

Would something like this work in column E?

Please Login or Register  to view this content.

[JenC]

When I tried that it gave me the greatest date that I had, even if there was no data next to it. It gave me 12/31/08 eventhough I only had data through 4/1/08.

Any other ideas?

[TIPPYS]

I'm not understanding what you want to do with the max date. are you just trying to get a running total in Column E? look at the example and help me understand. where are the dates that you are referencing?

[JenC]

In column A I have 527 days that somebody could use FMLA (which is a rolling 12 months). In Column C, I have the hours used for that day and in Column E is my rolling total.

On the 2nd sheet named calendar(which is where all this information is pulled from) I have a "total hours used" and "total hours remaining" spot that I would like to update automatically.

I cannot figure out how to get this to update based on the last day FMLA was used.

Does this help explain me better?

Thank you!

[daddylonglegs]

In Column A I have a list of dates. When somebody uses FMLA I will enter the hours used into Column C. I need excel to search for the max date with a value in C and give me the total which will be in in Column E of the same row.

Try this array formula

=INDEX(E2:E100,MIN(IF(A2:A100=MAX(IF(C2:C100<>"", A2:A100)),IF(C2:C100<>"",ROW(A2:A100)-ROW(A2)+1))))

needs to be confirmed with CTRL+SHIFT+ENTER

[JenC]

It worked.

Thank you so much.

[daddylonglegs]

I probably made that a little more complex than it needs to be, though. You could also try

=INDEX(E2:E100,MATCH(1,(A2:A100=MAX(IF(C2:C100<>"",A2:A100)))*(C2:C100<>""),0))