Formula Challenge: Find the First Transaction for Each Month
Bottom line: In this Excel formula challenge you will learn how to write a formula to find the first date for each month in a set of data.
Skill level: Intermediate
Video Explanation of the Challenge
Click here to jump down to the Solution Videos
Raylene, a member of The Ultimate Lookup Formulas Course, asked a great question about one of the assignments in the course.
For this challenge, the company's sales team has competition to see who can make the first sale of the month. It's our job to create a report with the list of names for each month's winner.
This task is fairly easy if we can sort the data in the Sales Table. But what if we can't sort it? What would the formula look like then?
So, my challenge to you is to write a formula in column C to return the name of the sales person that made the first sale in each month.
Each row in the Sales Table is a transaction. The Date column (column F) contains the transaction date. Use the Date column to find the first sale for each month of the year, and return the sales person's name from column G.
The only rule is that you cannot sort the Sales Table.
The following image shows the results you are trying to produce. Each name is the first person to make a sale in the month.
Download the Files
Download the Excel file that contains the challenge.
Note about Excel Tables
The file uses Excel Tables. If you're not familiar with Excel Tables yet, you can turn off the structured reference Table formulas, and complete the challenge with regular formula references.
Here is a video on a Beginner's Guide to Excel Tables if you want to learn more about this awesome feature of Excel.
I added a solution file above that uses regular range references ($H$8:$H$1007) instead of structured reference Table formulas (tblSales[Date]). You can use that file if you are not familiar with Table formulas yet.
The Solution Videos
The videos below contain explanations for some of the most popular solutions that were submitted in the comments. The solution file can be downloaded in the section above.
I want to say a big THANK YOU to everyone that participated. We all learned a lot from your solutions.
Here's a link to the playlist of the videos on YouTube if you'd prefer to watch them there.
Video #1: Array Formulas Explained
The most popular solution was using a MIN IF array formula combined with VLOOKUP or INDEX MATCH. In this first video I explain the basics of an array formula. I step through how they calculate on a range of values, and how to use Ctrl+Shift+Enter to enter the formula in Excel.
Video #2: VLOOKUP or INDEX MATCH with MIN IF Array
Both techniques return the same result. However, INDEX MATCH is less prone to errors when columns are added/deleted. We can also use INDEX MATCH to return a value to the left of the Date column.
Checkout my free 3-part video series on the lookup formulas to learn more about these awesome functions.
Video #3: The AGGREGATE Function
The AGGREGATE function is a great non-array solution. That means we do NOT have to use Ctrl+Shift+Enter when entering this formula. This is great because most users don't know or understand how to modify array formulas.
For this technique we use the Small function in AGGREGATE to return the smallest value. The Small function has an optional [k] argument that allows us to return the 2nd, 3rd, etc. place value. This makes the formula more versatile if our sales competition gets new rules.
We combine AGGREGATE with VLOOKUP or INDEX MATCH to return the salesperson name.
The AGGREGATE function was introduced in Excel 2010 for Windows, and is available on all Windows and Mac versions after 2010.
Video #4: The New MINIFS Function
The MINIFS function was introduced in Excel 2016 and allows us to calculate a min if based on multiple criteria WITHOUT an array formula. We do NOT have to use Ctrl+Shift+Enter for MINIFS.
It's similar to a SUMIFS or COUNTIFS formula, and is very easy to write. It's probably the easiest solution for this challenge. However, MINIFS is only available on the latest versions of Excel 2016 and you will probably need to be on an Office 365 subscription to get it.
Video #5: Bonus – AGGREGATE with Multiple Criteria
We can also use the same technique with AGGREGATE to calculate a min if based on multiple criteria. This is still a non-array formula, meaning we do NOT have to use Ctrl+Shift+Enter.
And the Winner Is…
Like everything in Excel, there are many ways to solve this problem. This is a great learning experience that should help add a few new techniques to your formula toolbox.
Each solution comes with it's own pros & cons, which I explain in the videos above. I like the AGGREGATE function because of it's availability and versatility.
It's a non-array formula, so we don't have to use Ctrl+Shift+Enter, which is another plus. However, it's still an advanced formula that the average user hasn't seen before. So it will require some explanation if other people are going to be using your file.
MINIFS is the easiest solution, but not widely available if you or your users are on an older version of Excel.
Additional Links Mentioned in the Videos
- How Dates Work in Excel: The Calendar System Explained + Video
- How to Turn OFF Structured References in Excel Table Formulas
- VLOOKUP Example Explained at Starbucks
- VLOOKUP & MATCH: A Dynamic Duo
- The INDEX Function: A Road Map For Your Spreadsheet
- Free 3-part video series on the lookup formulas (VLOOKUP, INDEX, MATCH, & more)
- Free eBook on 10 Excel Formula Pro Tips
Submit Your Answer
Please leave a comment below and paste in your formula solution. There are many ways to go about this, so don't worry if your solution is different from others. This is just a way for everyone to learn from each other.
Also, don't scroll down and look at the other answers until you try it. 😉
Good luck! 🙂