Microsoft

How to find the latest (or earliest) date and a corresponding value

Use this combination of Excel functions to find the latest date and return a corresponding value.

You probably know how to use the MAX() and MIN() functions, to return the latest and earliest dates, respectively, in a range. But, how would you grab a corresponding value for that date? For instance, let's suppose you want to know the amount sold for the latest date in the sheet shown below.  In the case of a simple sheet, like this one, you can easily see the answer, but that's not a solution.

Returning the latest date is simple enough. Enter =MAX(range), which in this case is A2:A9. The latest date is November 14.

The value you're really after, is the sold amount for November 14. Excel's VLOOKUP() function finds a specific value in the leftmost volume of a data range and returns a value to the right in the same row. How many columns to the right depends on your specifications.

In this case, you want to find the latest date, not the literal date November 14. So, MAX() is the first argument:

=VLOOKUP(MAX(A2:A9,

The data range is the next:

=VLOOKUP(MAX(A2:A9,A2:C9,

The last argument is the column offset. In this case, the value you want to return is two columns to the right of the lookup value (the date), so it's the third column in the data range:

=VLOOKUP(MAX(A2:A9),A2:C9,3,0)

The last argument, 0, forces VLOOKUP() to find an exact match. For more on this argument, read Troubleshoot VLOOKUP() formula gotchas.

It's just as easy to find the earliest date. Simply substitute the MIN() function for the MAX() function.

Keep in mind that the lookup range (column A) contains unique values. You can use this solution with duplicate lookup values, but VLOOKUP() will use the first matching value.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

4 comments
Penelopy
Penelopy

I came across a more difficult case.


My data is organized like this:


123  1-Jan

123  2-Jan

456  15-Jan

789  1-Jan

123 14-Jan


I need the result to be 123 14-Jan 


any ideas on how to find that?


thanks in advance

Penelopy
Penelopy
Penelopy

I came across a more difficult case.


My data is organized like this:


123  1-Jan

123  2-Jan

456  15-Jan

789  1-Jan

123 14-Jan


I need the result to be 123 14-Jan 


any ideas on how to find that?


thanks in advance

Penelopy

DBlayney
DBlayney

Personally, I would use =SUMIF(A2:A9,MAX(A2:A9),C2:C9). This has the advantage that if the maximum date occurs twice in the date column, the total is deleivered rather than just the value for the first occurrence

jbenton
jbenton

=MAX(C2:C9*(A2:A9=MAX(A2:A9))) will do a similar job when entered as an array function (ie with ctrl-sh-enter) Has the advantage that it will find the larger of two values against the same date, can be adapted to use more criteria (eg a particular person) and it can be used to look to the left instead (though this could also be achieved using an INDEX and MATCH combination and I can't think I'd really use it unless I were trying to be deliberately obscure!)

Editor's Picks