Get date associated with last entry

Get date associated with last entry

Generic formula 

=LOOKUP(2,1/(row<>""),header)

Related formulas 

Get value of last non-empty cell

Lookup latest price

Explanation

If you have a table with dates across the top, and rows below that contain entries of some kind, you can retrieve date associated with the last entry with a formula that uses the LOOKUP function.

In the example shown the formula in H5 is:

=LOOKUP(2,1/(C5:G5<>""),C$4:G$4)

How this formula works

Working from the inside out, the expression C5:G5<>"" returns an array of true and false values:

{FALSE,TRUE,FALSE,FALSE,FALSE}

The number 1 is pided by this array, which creates a new array composed of either 1's or #DIV/0! errors:

{#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!}

This array is used as the the lookup_vector.

The lookup_value is 2, but the largest value in the lookup_array is 1, so lookup will match the last 1 in the array.

Finally, LOOKUP returns the corresponding value in result_vector, from the dates in the range C$4:G$4.

Note: results in H are dates formatted with the custom format "mmm" to show an abbreviated month name only.

Zeros instead of blanks

You might have a table with zeros instead of blank cells:

In that case, you can adjust the formula to match on values greater than zero like so:

=LOOKUP(2,1/(C5:G5>0),C$4:G$4)

Get date associated with last entry

Generic formula 

=LOOKUP(2,1/(row<>""),header)

Related formulas 

Get value of last non-empty cell

Lookup latest price

Explanation

If you have a table with dates across the top, and rows below that contain entries of some kind, you can retrieve date associated with the last entry with a formula that uses the LOOKUP function.

In the example shown the formula in H5 is:

=LOOKUP(2,1/(C5:G5<>""),C$4:G$4)

How this formula works

Working from the inside out, the expression C5:G5<>"" returns an array of true and false values:

{FALSE,TRUE,FALSE,FALSE,FALSE}

The number 1 is pided by this array, which creates a new array composed of either 1's or #DIV/0! errors:

{#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!}

This array is used as the the lookup_vector.

The lookup_value is 2, but the largest value in the lookup_array is 1, so lookup will match the last 1 in the array.

Finally, LOOKUP returns the corresponding value in result_vector, from the dates in the range C$4:G$4.

Note: results in H are dates formatted with the custom format "mmm" to show an abbreviated month name only.

Zeros instead of blanks

You might have a table with zeros instead of blank cells:

In that case, you can adjust the formula to match on values greater than zero like so:

=LOOKUP(2,1/(C5:G5>0),C$4:G$4)