Find the Largest (Latest) Date in Each Group in Google Sheets (3 Easy Formulas)

There are multiple ways to find the largest (latest) date in each group in Google Sheets using formulas. The best solutions involve the QUERY, MAXIFS, and SORTN functions. In this guide, we’ll explore all three methods.

Where Is This Useful?

Finding the latest date in each group is crucial when dealing with time-based data. Here are some practical use cases:

  • Track the most recent purchase or transaction per customer
  • Get the latest status update in a project
  • Find the last payment or invoice date per client
  • Identify the last recorded activity in a user log

Sample Data

Here’s a sample Sales History dataset with multiple customers:

Sample Data

Goal: Extract the largest (latest) date per customer.

1. Find the Largest Date in Each Group Using QUERY

The QUERY function is the easiest way to get the latest date per group in one step.

Formula:

=QUERY(A1:B, "SELECT MAX(A), B WHERE A IS NOT NULL GROUP BY B", 1)
Example of Using the QUERY Formula to Find the Largest Date in Each Group in Google Sheets

Steps to Use:

  1. Select a blank cell and enter the formula.
  2. Modify column references:
    • Replace A1:B with your actual data range.
    • Replace A with the date column letter.
    • Replace B with the group column letter.
  3. Press Enter—this will return the latest date per group.

Pros: Simple and doesn’t require additional columns.

Limitation: Only returns the date and group column (not other columns).

2. Find the Largest Date in Each Group Using MAXIFS

The MAXIFS function is another efficient method. It works by filtering the maximum date for each unique group.

Step 1: Get Unique Groups

Enter this formula in cell E2 to extract unique customer names:

=UNIQUE(B2:B)

Step 2: Find the Latest Date per Group

Enter this MAXIFS array formula in F2:

=MAP(E2:E, LAMBDA(val, IF(val="",,MAXIFS(A2:A, B2:B, val))))

Explanation:

  • E2:E -> Contains unique customer names.
  • A2:A -> The date column.
  • B2:B -> The customer column (group).

Pros: Works dynamically with multiple groups.

Limitation:

  • Requires an additional UNIQUE column for groups.
  • Uses LAMBDA, which may slow down performance in large datasets.

3. Find the Largest Date in Each Group Using SORTN

The SORTN function sorts and extracts the most recent entry per group.

Formula:

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

How to Modify the Formula:

  • SORT(A2:B, 2, TRUE, 1, FALSE)
    • Replace A2:B with the full data range.
    • Replace 2 with the column number of the group column.
    • Replace 1 with the column number of the date column.
  • SORTN(..., 9^9, 2, 2, TRUE)
    • Replace the last 2 with the column number of the group column.

Pros: Can return all columns, unlike QUERY.

Limitation: Requires sorting support first.

Comparison: Best Formula to Use?

FormulaProsCons
QUERYSimple, no extra columnsReturns only date & group column
MAXIFSWorks dynamically, easy to readRequires UNIQUE column and LAMBDA support
SORTNCan return all columnsNeeds sorting first

Best Choice?

  • If you need a quick one-step formula -> Use QUERY
  • If you want the dates and categories separately -> Use MAXIFS (it uses two standalone formulas).
  • If you need all columns returned -> Use SORTN

Final Thoughts

To find the largest (latest) date in each group in Google Sheets, you can use QUERY, MAXIFS, or SORTN based on your needs. The QUERY method is the easiest, but MAXIFS and SORTN offer more flexibility.

Which method works best for your dataset? Let me know in the comments!

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.

How to Extract the Last N Non-Blank Rows in Excel Dynamically

You can use the following formula to extract the last N non-blank rows in...

Count Consecutive Duplicates in Excel (Dynamic Array Formula)

Counting consecutive duplicates in Excel is useful for analyzing patterns, detecting repetitive sequences, and...

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

More like this

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Google Sheets: Extract Top N per Group from Query Aggregation

When working with grouped and aggregated data in Google Sheets, you might need to...

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.