Function Problems

Excel 365 Formula to Return the Earliest Row for Each Brand and Product

Jason MillerJason Miller Nov 12, 2026 869 views

Question details

Create one row for every unique brand-product combination and return the complete sales record associated with the earliest period.

Return the Earliest Sales Row for Each Brand and Product in Excel 365
Product
Microsoft Excel 365
Device & OS
not provided
Scenario
A sales table contains repeated brand and product pairs across multiple sales periods, and the user needs a de-duplicated list based on the first period for each pair.
Observed behavior
The desired result is a spilled table that includes the original headers and only the earliest matching record for every brand-product combination.
Before you start

Format the source range as an Excel Table and confirm that its name is Sales. Ensure Sales[period] contains real dates or consistently sortable period values, because MINIFS selects the lowest value.

Solution 1Recommended

Use a LET Formula to Return the Earliest Record for Every Brand-Product Pair

Use this Excel 365 dynamic-array formula when you want an automatically updating result directly on a worksheet.

The formula creates the unique Brand-Product pairs, finds the minimum period for each pair, matches those earliest records back to the Sales table, and spills the selected rows with their headers. If two rows have the same earliest period for one pair, this approach returns every matching earliest row.

1
Confirm the source table columns

Click any cell in the source data and open Table Design to confirm that the table is named Sales and includes the columns Brand, Product, and period.

2
Select a blank output cell

Click an empty cell outside the Sales table with enough blank cells below and to the right for the spilled results.

3
Enter the dynamic-array formula

Paste the following formula and press Enter: =LET(uBrands,UNIQUE(Sales[[Brand]:[Product]]),uBrand,CHOOSECOLS(uBrands,1),uProduct,CHOOSECOLS(uBrands,2),minPeriod,MINIFS(Sales[period],Sales[Brand],uBrand,Sales[Product],uProduct),flag,XMATCH(Sales[Brand]&"|"&Sales[Product]&"|"&Sales[period],uBrand&"|"&uProduct&"|"&minPeriod),VSTACK(Sales[#Headers],FILTER(Sales,IFNA(flag,0))))

4
Check the spilled results

Review the returned rows and verify that each Brand and Product combination appears for its earliest period. Remove any data blocking the spill range if Excel displays a #SPILL! error.

Use a LET Formula to Return the Earliest Record for Every Brand-Product Pair
Automatic results: When you add or edit records in the Sales table, the formula recalculates and updates the returned earliest records.
Work with dynamic spreadsheet formulas

Find the Earliest Brand-Product Record with WPS Spreadsheets

WPS Spreadsheets provides a familiar worksheet environment for organizing sales data and building formula-based summaries. Use a structured data range, dynamic-array functions where available, and sort or filter tools to identify the earliest sales record for each brand-product pair.

  1. 1. Open the sales workbook: Launch WPS Spreadsheets and choose Open to select the Excel sales workbook.
  2. 2. Prepare the source columns: Verify that the worksheet includes Brand, Product, and period columns and that period values are consistently formatted.
  3. 3. Create a results area: Click an empty worksheet area or insert a new sheet for the unique brand-product results.
  4. 4. Apply formulas or data tools: Enter compatible lookup and aggregation formulas, or use Data sorting and filtering tools to identify the first period for each Brand and Product.
Organize sales records by Brand, Product, and period in a clean spreadsheet table.Use formulas, sorting, and filters to isolate the earliest record for each product group.Open and edit Microsoft Excel .xlsx and .xls sales files with strong format compatibility.Use a lightweight, familiar spreadsheet interface for ongoing data analysis.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return more than one row for a brand-product pair?

It returns multiple rows when two or more records share the same minimum value in Sales[period] for that Brand and Product. Add a unique tie-breaker column if you need exactly one record.

What format should the period column use for MINIFS?

Use real Excel dates whenever possible. Text periods can work only when their text order matches chronological order, such as 2024-01, 2024-02, and 2024-03.

How do I fix a #SPILL! error in the earliest-row formula?

Click the cell showing #SPILL! and clear the cells identified as blocking the spill range. The formula must be entered outside the Sales table and needs empty cells for its output.

Can I return only selected columns instead of the entire Sales row?

Yes. Replace FILTER(Sales,IFNA(flag,0)) with a FILTER expression referencing only the desired table columns, such as FILTER(Sales[[Brand]:[period]],IFNA(flag,0)).

When should I use Power Query instead of the Excel formula?

Use Power Query when you repeatedly import or refresh sales data, need documented transformation steps, or prefer a load-and-refresh workflow over a live worksheet formula.