Identify last item in a list

[Greg]

I have a list of timestamps in column A (one per minute for a day,
total 1440 entries), and an associated list of TRUE/FALSE values in
column B:

12:00 AM FALSE
12:01 AM FALSE
12:02 AM TRUE

 

[Domenic]

Try...

=INDEX(A1:A7,MATCH(2,1/(B1:B7=TRUE)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Greg]

Works great! Thank you.

I added the necessary pieces to provide the desired error checking:

=IF(ISNA(INDEX(A1:A1440,MATCH(2,1/(B1:B1440=TRUE)))),
"No TRUE values found.",INDEX(A1:A1440,MATCH(2,1/(B1:B1440=TRUE))))

Greg

 

[Jason Morin]

I think that's a little overkill. Try:

=IF(COUNTIF(A1:A1440,TRUE)=0,"No TRUE values found",...)

HTH
Jason
Atlanta, GA

 

[Greg]

Re-read my original post. The "No TRUE values found" part is just the
error check. The main purpose of the function is to identify the
timestamp associated with the last TRUE entry.

Greg

 

[Biff]

Hi!

Jason's suggestion is more efficient and also results in a
shorter formula!

Biff

 

[Domenic]

Greg,

If I'm not mistaken, I believe Jason's suggestion is to use COUNTIF()
rather than ISNA(INDEX(...)) for error checking...

=IF(COUNTIF(B1:B1440,TRUE)=0,"No TRUE values
found",INDEX(A1:A1440,MATCH(2,1/(B1:B1440=TRUE))))

Probably a little more efficient, and a good suggestion at that.

Hope this helps!

 

[Greg]

I see it now. Thanks to everyone for all the help.

Greg