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.
