How To Find The Last Date Less Than A Control Date In Excel

In this tutorial we are going to use the MAX and IF functions in an array formula, along with INDEX and MATCH to extract the last date in a list of events that is less than a control date and the event related to that date.

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.

So here I have a list of dates and events for six different items along with a control date:

I want to populate this section with the date that is the latest, but less than the control date, along with the associated event:

In the “Answer” cell we are going to use an array formula utilizing the MAX function and IF function:

{=MAX(IF(B2:O2<P2,B2:O2))}

And in the “Related Event” cell we are going to use an INDEX/MATCH combination formula:

=INDEX(B2:O2,1,MATCH(Q2,B2:O2,0)+1)

Take a few minutes and watch the video to see how these work to accomplish our goal.

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!

, , , , , ,

One Response to How To Find The Last Date Less Than A Control Date In Excel

  1. M. A. Jogpur August 16, 2017 at 10:45 pm #

    Great

Leave a Reply