How to Find the Latest (or Earliest) Date Along With a Corresponding Value in ExcelFind the Corresponding Value with Latest (or Earliest) Date in Excel
Some of the datasets that you work with in Excel can involve dates. For example, you could be working with the daily sales data of a store. Or you could be working with the general journal of a company. Whatever the case, working with dates in Excel feels a bit (maybe a tad) different from working with only numbers or text.
But it isn’t really that complicated. Excel still sort of treats dates as numerical values. You can add or subtract them together. You can add a numerical value, such as adding 1 to the date to get tomorrow’s date. And since they’re sort of numbers still, you can use functions on them that work on numbers.
Like the MAX and MIN functions for instance. If either of these functions is used on a set of numbers, it will return the highest or the lowest numerical value from the set. Now, what would happen if you were to use either of these functions on a set of dates?
Let’s find out.
Find the Latest or Earliest Date with the MAX and MIN Functions
When you use the MAX function on a set of dates, it will return the latest date from among the set. To illustrate, here’s the MAX function in action:
Here we have a set of dates located in cells A2:A26. Using the MAX function in this set, we find that the latest date is 1/26/2023.
On the other hand, when you the MIN function on a set of dates, it will return the earliest date from among the set. Let’s see it used on the same dataset:
Using the MIN function on cells A2:A26 gives us the earliest date, which is 1/2/2023.
So to summarize, using the MAX function on a set of dates gives you the latest date. On the other hand, using the MIN function on a set of dates gives you the earliest date.
Now let’s see what we can with do this information.
Find a Value that Corresponds with the Latest (or Earliest) Date
The following is the daily ticket sales data of a hypothetical cinema. In column A, we set the when the tickets are sold (date). In column B, we see the number of tickets sold. Finally, in column C, we see the sales amount.
What we want to find here is the sales data for the latest and earliest dates. To do so, we’ll be using the MAX and MIN functions with the following functions: VLOOKUP, XLOOKUP, INDEX, and MATCH.
Use VLOOKUP to Find Value With the Latest (or Earliest) Date
The VLOOKUP function is typically used if you want to look up a certain value within a table or range by row. The caveat is that it only properly works if the lookup value is located in the column that is anywhere to the left of the results column. To be safe, the lookup value should be located in the leftmost column of the table or range. Also, if the lookup range contains duplicate values, VLOOKUP will only return the result that matches the first instance of the lookup value.
The formula to use VLOOKUP is as follows:
=VLOOKUP(lookup_value,reference_array,col_num,[range_lookup])
Where
lookup_value – refers to the value or criterion which the function will base on for its search
reference_array – refers to the table or range from which the function will search for the lookup_value
col_num – refers to the column index number from which the result value is located. (e.g. if it is in the third column of the reference_array, set col_num to 3)
[range_lookup]) – specifies whether the result will be an approximate or exact match. Set it to TRUE if you want an appropriate match. Set it to FALSE if you want an exact match. This is optional and has a default value of TRUE
How to find the value that corresponds with the latest date
To find the value that corresponds with the latest date, we’ll set lookup_value to MAX(lookup column). Suppose we want to find out the number of tickets sold and the sales amount on the latest date.
The formula to find out the number of tickets sold will be:
=VLOOKUP(MAX(A2:A26),A1:C26,2,FALSE)
The formula to find out the sales amount will be:
=VLOOKUP(MAX(A2:A26),A1:C26,3,FALSE)
How to find the value that corresponds with the earliest date
To find the value that corresponds with the earliest date, we’ll set lookup_value to MIN(lookup column). Suppose we want to find out the number of tickets sold and the sales amount on the earliest date.
The formula to find out the number of tickets sold will be:
=VLOOKUP(MIN(A2:A26),A1:C26,2,FALSE)
The formula to find out the sales amount will be:
=VLOOKUP(MIN(A2:A26),A1:C26,3,FALSE)
Use XLOOKUP to Find Value With the Latest (or Earliest) Date
The XLOOKUP function is sort of an improved version of the VLOOKUP function. It no longer has the limitation where the lookup array must be to the left of the results array.
However, it still has the same limitation where it will only return the result that matches the first instance of the lookup value if there are duplicate values. The biggest issue with XLOOKUP currently is its availability.
As of the publishing date of this article, XLOOKUP is only available in the 2021 and Office 365 versions of Excel.
The formula for using XLOOKUP is as follows:
=XLOOKUP(key_value,lookup_array,results_array)
Where
key_value – refers to the key value or criterion which the function will base on for its search
lookup_array – refers to the array from which the function will look for the key_value
results_array – refers to the array from which the function retrieves the value that matches the row of the key_value
How to find the value that corresponds with the latest date
To find the value that corresponds with the latest date, we’ll set key_value to MAX(lookup_array). Suppose we want to find out the number of tickets sold and the sales amount on the latest date.
The formula to find out the number of tickets sold will be:
=XLOOKUP(MAX(A2:A26),A2:A26,B2:B26)
The formula to find out the sales amount will be:
=XLOOKUP(MAX(A2:A26),A2:A26,C2:C26)
How to find the value that corresponds with the earliest date
To find the value that corresponds with the earliest date, we’ll set key_value to MIN(lookup_array).
Suppose we want to find out the number of tickets sold and the sales amount on the earliest date.
The formula to find out the number of tickets sold will be:
=XLOOKUP(MIN(A2:A26),A2:A26,B2:B26)
The formula to find out the sales amount will be:
=XLOOKUP(MIN(A2:A26),A2:A26,C2:C26)
Use INDEX and MATCH to Find Value With the Latest (or Earliest) Date
The combination of INDEX and MATCH works just like the XLOOKUP function. However, unlike XLOOKUP which has an availability issue, the INDEX and MATCH are widely available.
Thus, if you’re not using the latest version of Excel, then you can use the combination of INDEX and MATCH in place of XLOOKUP.
The formula for this combination is as follows:
=INDEX(results_array,MATCH(key_value,lookup_array,0))
Where
key_value – refers to the key value or criterion which the function combination will base on for its search
lookup_array – refers to the array from which the function combination will look for the key_value
results_array – refers to the array from which the function combination retrieves the value that matches the row of the key_value
How to find the value that corresponds with the latest date
To find the value that corresponds with the latest date, we’ll set key_value to MAX(lookup_array).
Suppose we want to find out the number of tickets sold and the sales amount on the latest date.
The formula to find out the number of tickets sold will be:
=INDEX(B2:B26,MATCH(MAX(A2:A26),A2:A26,0))
The formula to find out the sales amount will be:
=INDEX(C2:C26,MATCH(MAX(A2:A26),A2:A26,0))
How to find the value that corresponds with the earliest date
To find the value that corresponds with the earliest date, we’ll set key_value to MIN(lookup_array).
Suppose we want to find out the number of tickets sold and the sales amount on the earliest date.
The formula to find out the number of tickets sold will be:
=INDEX(B2:B26,MATCH(MIN(A2:A26),A2:A26,0))
The formula to find out the sales amount will be:
=INDEX(C2:C26,MATCH(MIN(A2:A26),A2:A26,0))
Conclusion
In this article, I was able to show you how to find the latest or earliest date from among a set of dates (with the MAX and MIN functions).
Along with that, we also discussed the different functions you can use to find a value that corresponds with the latest or earliest date.
I hope that you’ll be able to use your learning here in your future endeavors.