How To Find The Last Match In An Array Based On Multi Criteria Including Date In Excel

In this tutorial we’ll look at how to dynamically pull the salaries of all employees from a list, for the period of the date selected.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here is our scenario. I have a list of employees and their salaries at specific dates. This is sorted in chronological order by date. As an employee gets an increase, they are just added to the bottom of the list:

122915-1

I also have a drop down list that has the first of each month for the year:

122915-2

And below that is the area that I want to pull in the salaries based on the date I choose from the drop down list. Notice I have created a dynamic header for the list that will change based on the date I choose from my drop down list. I’ve highlighted the formula, which concatenates the text and a TEXT formula formatting the date in F1 in the “mm/dd/yyyy” format:

122915-3

The formula we are using for this is:

{=MAX(IF($A$2:$A$20=E4,IF($B$2:$B$20< =$F$1,$C$2:$C$20)))}

It incorporates the MAX and two embedded IF functions. Notice also the curly brackets around the formula, indicating that it is an array formula that needs to be entered using Ctrl + Shift + Enter, not just Enter.

How this formula works is this: The first IF statement creates a list of TRUE and FALSE as it matches the name in the cell in column E to the list of names in column A:

122915-4

The second IF statement returns a list of salaries that are listed for dates less than or equal to what is in cell F1. The zeros at the end are just due to the empty rows at the bottom of the list that I’ve left for additional entries:

122915-5

The result when the array formula compares the TRUE locations with values from the salaries, generates a list that the MAX function will then take the largest from:

122915-6

In this case, with Tony and January 1, 2015 as the date, there is only one valid choice – 2,200. However, after we copy the formula down and change the date to October 1, 2015, William will show three choices for the MAX function to choose from:

122915-7

And will return the largest – 3,500. As more are added to the list, it will update automatically, as long as the range is properly set.

122915-8

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , , ,

No comments yet.

Leave a Reply