Find the last date of an activity

We know that using VLOOKUP, we can find a value corresponding to a given item. For example Sales of x. But what if you have multiple sales for each item and you want the last value?

Today lets understand how to find the last date of an activity, given data like this:

[Note: thanks to SDK who asked this question in a comment]

Like everything else in Excel, there are multiple ways to finding last date. If cats can use computers, they would hate Excel. You see, Excel is overflowing with unlimited ways to skin a cat.

Method 1: Using LOOKUP formula

Assuming the data is in range,

• Dates in $C$3:$F$3
• x marks in C4:F4

We can use =LOOKUP(“y”, C4:F4, $C$3:$F$3) to find the last date.

Why find y? Simple, since our data has “x” against date & name combinations, we just find the next letter (y). So LOOKUP formula stops looking after finding the last x. You see, LOOKUP formula assumes the list is sorted, so if it cannot find a match (in our case – y), it would return the closest match (ie, the last x).

Related: Comprehensive guide to Excel VLOOKUP & other LOOKUP formulas.

Method 2: Using MAX & SUMPRODUCT

I am like Gus Portokalos in My Big Fat Greek Wedding movie. SUMPRODUCT is my Windex. I use it for tough data, complex questions, sores, minor ailments & occasional car dents.

In this case, a formula like =SUMPRODUCT(MAX(($C$3:$F$3)*(C4:F4=”x”))) would do the job.

To be honest, just MAX(($C$3:$F$3)*(C4:F4=”x”)) would do too, but then you have to CTRL+Shift+Enter it.

How does it work? Since dates are just numbers, we take the dates & multiply them wherever there is x. So, it would be,

{41091,41092,41093,41094}*{TRUE,TRUE,FALSE,FALSE}

Which will be,

{41091,41092,0,0}

Then we find the maximum of this, which is 41092 (the number corresponding to 2-jul-2012).

Since this is an array operation, we can either CTRL+Shift+Enter it or wrap it inside SUMPRODUCT, like the G(r)eeks do. And that gives the answer.

More on this: Writing MAXIF formula in Excel, Introduction to SUMPRODUCT

Download Example Workbook

Click here to download example workbook. I have a bonus homework form you in there. Go ahead and solve it.

Your Homework

Time for a quiz. How would you find last date for a given name, if your data is like this?

Go ahead and share your answer.