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.

Editor's Picks