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:

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)

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?

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

• Find the Last Row in Each Group in Google Sheets
• Extract the Last Entry of Each Item by Date in Google Sheets
• Retrieve the Earliest or Latest Entry Per Category in Google Sheets
• Extract First N Rows From Each Group in Google Sheets