Lookup the Most Recent Past Date Based on Criteria - Excel Formula

In this video, we use an Excel formula to lookup the most recent date in the past based on criteria. Our example is to return the date of the most recent class, specified by a cell value.
This is an intriguing challenge which came about from a question on one of my other videos.
It utilises a couple of helper columns with formulas before we use an INDEX and MATCH formula combination to return the date.
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/2JvnnRv
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2t3netw
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2viGg3J
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1

[@BillSzysz1]

Accidentally here (beacause of Oz du Soleil). A very nice channel :-)
This job can be solved by LOOKUP function without helper columns.
=LOOKUP(2,1/((A2:A13

[@Unirotovibe]

Or maybe like this (translated from my french Excel, hope there's no mistake)
=MAXIFS(A2:A13;B2:B13;F2;A2:A13;"

[@Computergaga]

Thank you Bill.

[@Computergaga]

Excellent. Thanks Unirotovibe.

[@anjalidev5955]

@@Unirotovibe thank you

[@wayneedmondson1065]

Hi Alan.. cool trick.. thanks! I've been trying to work out a single formula solution. Came close, but couldn't get it. Then, I noticed the tip below from Bill Szysz.. excellent!! The aging but still useful LOOKUP() function to the rescue (for those still without DAFs). Except, I think you need to make it

[@Computergaga]

Thanks Wayne. Yes I wanted a solution that anybody could use and DAF's and the MAXIFS function are only available in recent versions.

[@ashoksahu9546]

Sir Excellent video. Very nice.

[@Computergaga]

Thank you Ashok. Much appreciated.

[@VS-rh8rq]

Nice

[@Computergaga]

Thank you Vimal.

[@muhinda26]

How can i find a date in the left column of a transaction lookup?

[@danieldevinney9247]

God I was going crazy trying to figure out a solution like this and this was a life saver! Thank you so much!

[@Computergaga]

Happy to help 👍

[@aj_195]

Pls make a video in how to retrieve second last recent date

[@TutanComputerGuide]

Awesome Sir

[@Computergaga]

Thank you.

[@colinhuntley9729]

Hi Alan, it is always grand to watch and listen to your videos. If have used your solution into a similar database. Having said that, i have come across the fact that i have two courses on the same date. The formula however only shows the first date/course. I have been trying to figure this out for quite a few days now, tring ROWS, SMALL, LARGE etc. alasl with no success. Could you maybe give me a tip which could out me back on track? Maybe you could recommend a YTvideo? Would be grateful for any assistance which you could giv me. I realise that you must get inundated with similar questions, so please let me know if you cannot answer. Cheer, Colin