How to Lookup Latest Dates in Google Sheets

Lookup the latest date is not simply about finding the maximum date in a column; it is a broader topic in Google Sheets. It allows you to get a value based on the most recent date or look up a search key in the latest entry row. For this, you can use the XLOOKUP function in Google Sheets.

I prefer XLOOKUP over VLOOKUP (both are lookup functions with vertical lookup capability in unsorted data) because of its ability to lookup any column in a range. VLOOKUP is limited to looking up in the first column.

Lookup the Latest Date in a Column in Google Sheets

Assume you have various items in one column and their receipt dates in another column. To find the item received on the most recent date, you can use the following formulas.

In this example, I consider the dates in column B and items in column A starting from row #2, as row #1 is for titles.

=XLOOKUP(MAX(B2:B), B2:B, A2:A)

This formula looks up the latest date returned by the MAX function from top to bottom in the range B2:B and returns the corresponding value from A2:A.

Lookup the Latest Date in a Column using XLOOKUP

To lookup the latest date from the bottom of a range to the top, you may use the following XLOOKUP formula:

=XLOOKUP(MAX(B2:B), B2:B, A2:A,,,-1)

Both formulas will get the same result if there are no multiple occurrences of the max date in the column.

To get all occurrences, you should rather depend on the FILTER formula as XLOOKUP is not suitable in this case:

=FILTER(A2:A, B2:B=MAX(B2:B))

The FILTER function filters the range A2:A where the range B2:B matches the max date in B2:B.

Lookup the Latest Date in a Column using FILTER

Lookup a Search Key in a Column in the Latest Date Row

This is a different scenario. Assume you want to lookup when you last received an item as well as its quantity. Here we should adopt a different approach.

The following formula looks up “Sunglasses” in A2:A and returns the latest receipt date from B2:B.

=XLOOKUP("Sunglasses", SORT(A2:A, B2:B, FALSE), SORT(B2:B, 1, FALSE))
Lookup a Search Key in a Column in the Latest Date Row

In this, the search key is “Sunglasses.”

How does this formula lookup this key in the latest date row?

I have sorted the search key column, i.e., A2:A, based on the date in descending order and the result column, i.e., the dates, also in descending order.

  • SORT(A2:A, B2:B, FALSE) – sorts A2:A based on B2:B in descending order
  • SORT(B2:B, 1, FALSE) – sorts B2:B in descending order.

The result will be a date value. Navigate to the result cell and apply Format > Number > Date.

Note for Excel users: This formula may not work in Excel; you may use SORTBY instead of SORT for the search key (lookup) column sorting.

If you want to lookup the search key “Sunglasses” and return its latest delivery quantity, you should use the formula as follows:

=XLOOKUP("Sunglasses", SORT(A2:A, B2:B, FALSE), SORT(C2:C, B2:B, FALSE))

Where:

  • Search Key: “Sunglasses”
  • Lookup Range: SORT(A2:A, B2:B, FALSE) – sorts items based on dates in descending order
  • Result Range: SORT(C2:C, B2:B, FALSE) – sorts quantities based on dates in descending order

When you have the same item received multiple times, you should use FILTER instead:

=FILTER(A2:C, A2:A="Sunglasses", B2:B=MAX(IF(A2:A="Sunglasses", B2:B,)))

The FILTER function filters the range A2:C where A2:A is equal to “Sunglasses” and B2:B is equal to the max date in the date range, ensuring that only the latest entry is included.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.