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.

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Hyperlink to Jump to the Last Used Row in Excel

In a vertical range, you can create a hyperlink to jump to the last...

Find the Last Used Row’s Last Value Address in Excel

In a large vertical dataset in Excel, how do you find the cell address...

Find the Last Used Row Number in Excel

When working with large datasets such as sales records, purchase data, or bills of...

More like this

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

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.