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).
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,
Which will be,
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.
Download Example Workbook
Click here to download example workbook. I have a bonus homework form you in there. Go ahead and solve it.
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.
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« How to make a Spoke Chart||Creating a Masterchef Style Clock in Excel [for fun] »|