Learning how to look up the earliest dates in Google Sheets is essential, especially in fields like logistics. Dates play a crucial role in many aspects of our lives, from scheduling to planning.

There are countless scenarios where you might need to find the earliest date. For example, if you work as a logistics coordinator, handle material purchases, or sell products on online marketplaces, identifying the earliest dates can be vital.

Suppose you’ve ordered an item that arrives in different lots on different days. By entering the schedule of all arrivals, you can use my tips to find the earliest arrival date. This helps you make arrangements to receive the materials in time.

How to Lookup Earliest Dates in Google Sheets?

There’s no need to go through dates manually to find the earliest one in Google Sheets. I’ll show you a formula to make this task much easier.

When working with multiple items and their delivery dates, you might need an ArrayFormula to return the earliest date for each item. However, if there is only a single item in Column A with multiple delivery dates, you can use Google Sheets’ MIN or SMALL functions. For simpler cases like this, refer to my linked tutorial: Find Minimum Value and Return Value from Another Column in Google Sheets.

Advanced Case: Multiple Items with Delivery Dates

In this example, we’ll use the VLOOKUP function combined with SORT and SORTN formulas to find the earliest delivery dates for each item.

Sample Data

• Items are listed in A2:A.
• Delivery dates are listed in B2:B.
• The itmes to lookup are in E2:E.

Formula to Lookup Earliest Dates in Google Sheets

Here’s the formula I used in Cell F2:

=ArrayFormula(
   IFNA(
      VLOOKUP(
         E2:E, 
         SORTN(SORT(A2:B, 2, TRUE, 1, TRUE), 9^9, 2, 1, TRUE), 
         2, 
         FALSE
      )
   )
)

Notes:

• Select the result range and apply Format > Number > Date to display the results as properly formatted dates.
• This formula ensures that any unmatched values return blank cells instead of errors, thanks to the IFNA function.

Key parts of the formula:

• The SORT and SORTN combination creates a lookup range.
• The VLOOKUP searches for the earliest dates based on unique items specified in E2:E.

Explanation of the Formula

1. SORT Function

=SORT(A2:B, 2, TRUE, 1, TRUE)

Syntax: SORT(range, sort_column, is_ascending, [sort_column2, …], [is_ascending2, …])

• Sorts the data in ascending order by delivery dates (Column 2)
• If dates are the same, items (Column 1) are sorted in ascending order.

This ensures that the earliest dates are placed at the top.

2. SORTN Function

=SORTN(SORT(A2:B, 2, TRUE, 1, TRUE), 9^9, 2, 1, TRUE)

Syntax: SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending], …)

• Removes duplicates in Column 1 by keeping only the first occurrence of each unique item.
• The 9^9 serves as a placeholder for the number of rows to return, ensuring all results are included regardless of the dataset size.

3. VLOOKUP Function

=ArrayFormula(VLOOKUP(E2:E, SORTN(SORT(A2:B, 2, TRUE, 1, TRUE), 9^9, 2, 1, TRUE), 2, FALSE))

Syntax: VLOOKUP(search_key, range, index, [is_sorted])

• search_key: The item names in E2:E.
• range: The output of the SORTN + SORT combination.
• index: The column index (2) where dates are located.
• is_sorted: FALSE ensures exact matches are found.

This formula returns the earliest delivery date for each item in Column E.

XLOOKUP Alternative to Lookup Earliest Dates

By combining SORT, SORTN, and VLOOKUP, you can efficiently find the earliest dates in Google Sheets. This method is particularly useful for large datasets with multiple items and delivery dates.

Of course, you can also use XLOOKUP by sorting the lookup range (Column A) and result range (Column B) to return the earliest dates. However, it has a limitation. Before discussing the drawback, here’s the XLOOKUP formula:

=ArrayFormula(
   XLOOKUP(E2:E, SORT(A2:A, B2:B, 1), SORT(B2:B),)
)

Drawback

Assume you have an additional column, such as Column C, that contains notes. The XLOOKUP function cannot return multiple columns in an array, whereas the VLOOKUP method can handle this scenario seamlessly.

To address this limitation, XLOOKUP would require the assistance of the MAP lambda function to expand its functionality. However, this approach is more complex and resource-intensive compared to the VLOOKUP method.

Resources

• Extract the Earliest or Latest Record in Each Category Based on Timestamp in Google Sheets
• Highlight Earliest Events Based on Date Column in Google Sheets
• Lookup Latest Value in Excel and Google Sheets
• How to Lookup Latest Dates in Google Sheets
• Formula to Combine Rows and Get Latest Values in Google Sheets
• Highlight the Latest Value Change Rows in Google Sheets
• How to Highlight the Latest N Values in Google Sheets
• Get the Latest Non-Blank Value by Date in Google Sheets