Excel Find the mosr recent date using vlookup

Murray Brown
Murray Brown used Ask the Experts™
on
Hi. I am using the following Excel formula to find a date using a serial number
in column K. The Registration_Table data will have more than one date against that
serial number. I want tofind the most recent date. How do I do this?


=IFERROR(VLOOKUP(K2,Registration_Table!A:I,3,0),"")
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try

=IFERROR(VLOOKUP(max(Registration_Table!A:a),Registration_Table!A:I,3,0),"")
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I think VLOOKUP is the wrong tool for this problem--it only ever returns one value.

Consider instead using MAXIFS function. You'll need Excel 2016/Office 365 or Excel 2019 perpetual.

=IFERROR(MAXIFS(Registration_Table!C:C,Registration_Table,Registration_Table!A:A,K2),"") 

Open in new window


If you have an older version of Excel (Excel 2010 or later), you may use the AGGREGATE function instead.
=IFERROR(AGGREGATE(14,6,Registration_Table!C2:C1000/(Registration_Table!A2:A1000=K2),1),"")

Open in new window

I restricted the range of cells being tested because this is an array formula (not requiring Control + Shift + Enter), and would be slow if applied to entire columns. If you are still stuck with Excel 2007, there is an equivalent Control Shift Enter formula using LARGE.
Rob HensonFinance Analyst
Commented:
Is your data is sequential order with oldest first? Will the most recent date be the last entry for a particular serial number?

If so, a quirk of vlookup is that if you tell it to do a close match rather than an exact match, it will return the last value where there are multiple entries for the lookup value.

Try:
=IFERROR(VLOOKUP(K2,Registration_Table!A:I,3,1),"")

If you can sort the data so that it is most recent first then the first entry will be the most recent and the original lookup formula will give the result you want.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks for the help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial