Find last date an occurance happened

[AL Haste]

Hello,

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]

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.

 

[vletm]

AL Haste
Hint: with uploaded Excel-file You would get quicker replies than ... any picture quiz.

 

[AL Haste]

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.

 

[p45cal]

See atached, cell Q2.

 

[AL Haste]

Wow p45cal, thank you a bunch.

Works like a charm.

 

[Peter Bartholomew]

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]

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]

@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.