Quick, clean, and to the point

Get date associated with last entry

Excel formula: Get date associated with last entry
Generic formula 

To retrieve a date associated with a last entry tabular data, you can use a a formula based on the LOOKUP function. In the example shown the formula in H5 is:


How this formula works

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


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


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:

LOOKUP formulas for ignoring zero values

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


Multiple criteria

You can extend criteria by adding expressions to the denominator with boolean logic. For example, to match the last value greater than 400, you can use a formula like this:

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.