Spill the Earliest Date for Each Unique Item in Excel
Question details
Return the earliest date for each unique item using a dynamic-array formula.

- 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.
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.
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.
In cell G3, enter =UNIQUE(C3:C32) to spill the unique items from column C.
In cell H3, enter =BYROW(G3#,LAMBDA(row,MIN(FILTER(D3:D32,C3:C32=row)))). The G3# reference includes the entire spilled list.
Select the spilled results in column H, then use Home > Number Format and choose a date format if the results appear as numbers.

Use SORT and VLOOKUP to return the first matching date
Use this alternative when your Excel version supports SORT, LET, and dynamic arrays.
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.

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.




