Function Problems

Spill the Earliest Date for Each Unique Item in Excel

Phi Hung VoPhi Hung Vo Nov 18, 2026 868 views

Question details

Return the earliest date for each unique item using a dynamic-array formula.

Spill the Earliest Date for Each Unique Item in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Items are listed in one column and their dates in another. The user wants a spilled list of earliest dates rather than formulas copied down.
Observed behavior
A single formula should return one earliest matching date for each item in a spilled unique-item list.
Before you start

Confirm that C3:C32 contains items, D3:D32 contains valid Excel dates, and the rows used for this calculation have no blank items or dates. Use an Excel version that supports the functions in your chosen formula, and leave enough empty cells below the output.

Solution 1Recommended

Use BYROW and FILTER to spill the earliest dates

Use this method when you have a spilled list of unique items and an Excel version that supports BYROW and LAMBDA.

1
Create the unique-item list

In cell G3, enter =UNIQUE(C3:C32) to spill the unique items from column C.

2
Enter the earliest-date formula

In cell H3, enter =BYROW(G3#,LAMBDA(row,MIN(FILTER(D3:D32,C3:C32=row)))). The G3# reference includes the entire spilled list.

3
Format the results

Select the spilled results in column H, then use Home > Number Format and choose a date format if the results appear as numbers.

Use BYROW and FILTER to spill the earliest dates
Keep the spill area clear: If Excel displays a #SPILL! error, clear cells blocking the output below G3 or H3. Blank date cells can be treated as zero, so exclude or complete them before calculating.
Free Microsoft Office alternative

Summarize Item Dates with WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with Excel files. You can use a pivot table to summarize dates by item, although support for the specific Excel dynamic-array formulas shown above may vary.

Summarize the earliest date for each item with a pivot table.Open and edit Microsoft Excel XLSX and XLS files for easier migration.Work in a familiar spreadsheet interface.Use a free, lightweight office suite.
QA img-9

Frequently Asked Questions

What does G3# mean in the formula?

G3# refers to the entire range spilled by the formula in G3. As the UNIQUE result changes size, the reference expands or contracts with it.

Why do the earliest dates appear as numbers?

Excel stores dates as serial numbers. Select the result cells and choose a date format from Home > Number Format.

Why am I getting a #SPILL! error?

The output area may contain existing values. Clear the cells below the formula and check that the formula is not inside an Excel table, where spilled array formulas are not supported.

What if Excel does not recognize BYROW or LAMBDA?

Your Excel version may not support those functions. If it supports LET, SORT, and dynamic arrays, try the SORT and VLOOKUP alternative; otherwise, use a copied-down formula or a pivot table.