• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find last date an occurance happened

AL Haste

New Member

I have a range of dates where occurrances happen (1 if it does and 0 if it does not).

The data is in rows.

I want to find the last time the occurance happend and extract the date to another cell.

Since I'm trying to count backwards I'm having trouble because I cannot make VLOOKUP or HLOOKUP work

It's probably super simple but for some reason I cannto wrap my brain around it.

Hope you can help.

Please see example data below:


AL Haste

New Member
Loking at my example it dawned on my:
Maybe the above example does not make sense.

Essentially, I want to extract the date 17/8-2019 since this is the last time the occurance happend.

AL Haste

New Member
Hi Vletm.

Thankyou for your feedback.

The data is part of a rather large spreadsheet but I've uploaded an example spreadsheet now.

Thank you again.

Peter Bartholomew

Well-Known Member
The first three are the existing solution from @p45cal, or as close as makes no difference. The 4th uses the property of LOOKUP of locating the final match of a sequence. The final formula is only usable within Office 365 which has a final parameter which determines whether to return the first of last match.


GraH - Guido

Well-Known Member
or without "CSE" array formula
=AGGREGATE(14,6,$A$1:$O$1/($A2:$O2=1),1), will return the largest date where the criterion is 1 in the range.

AL Haste

New Member
@Peter Bartholomew and @GraH - Guido Thank you so much for your suggestions.

I will look into your formulas as well.

For some reason, when pulling the data t another sheet, the formula messed up somehow and gave me the week after, but since I have weeknumbers also I could then go for that and subtract 1.

I'm really thankfor for the time you put into helping me solve my issue.

Thank you again.