Excel Formula to Return First and Last Values for Each File Name
Question details
The user needs an Excel formula to group records by file name (in column A) and extract the starting (first) and ending (last) values for related fields like hfn, sfn, and slot (in columns B:D).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Grouping tabular data by a specific identifier to dynamically extract the earliest and latest corresponding field values without manual filtering.
- Observed behavior
- The goal is to automatically generate a summary table in columns G:L containing a distinct list of file names along with their respective first and last associated values.
Ensure you are using a modern version of Excel or WPS Spreadsheet that supports dynamic array functions like UNIQUE, XLOOKUP, and HSTACK, as these are required for this automated data extraction method.
Use UNIQUE and XLOOKUP Functions
This is the most robust and highly recommended method. It uses the UNIQUE function to list distinct file names and the XLOOKUP function's search modes to find the first and last matches.
XLOOKUP features a built-in search_mode argument that allows you to specify whether to search from first-to-last or last-to-first. This makes extracting boundary values incredibly simple, even if your data is not sorted.
In cell G2, enter the formula =UNIQUE(A2:A100) to generate a distinct, spillable list of all file names from column A.
In cell H2, enter =XLOOKUP(G2#, A2:A100, B2:D100, "", 0, 1). The '1' at the end tells the function to search from the top down, returning the first encountered row for columns B through D.
In cell K2, enter =XLOOKUP(G2#, A2:A100, B2:D100, "", 0, -1). The '-1' at the end changes the search direction to bottom-up, returning the last encountered row for that file name.
To do this entirely in one formula, you can wrap the lookup functions inside HSTACK and CHOOSECOLS to directly output all columns G through L simultaneously.
Use the GROUPBY Function
If you are enrolled in the Office Insider program or using a version with the latest array functions, GROUPBY can instantly aggregate minimum and maximum values.
Filter Sorted Data by Streak Detection
A logic-based fallback method that involves detecting where a streak of identical file names begins and ends, then filtering the dataset.
Easily Extract First and Last Values with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE, XLOOKUP, and HSTACK, allowing you to group records and extract starting and ending values effortlessly without complex macros.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your file names and related values.
- 2. Extract distinct names: Click on a blank cell and use the =UNIQUE(A:A) function to generate a clean list of file names.
- 3. Fetch the first and last records: Use the =XLOOKUP() function, setting the search_mode argument to 1 for the first value, and -1 for the last value.
- 4. Review the summary: Press Enter to execute the formulas. WPS Spreadsheet will instantly spill the calculated results into the neighboring cells.
Frequently Asked Questions
Why does XLOOKUP return an error when searching for the last value?
Errors usually occur if your lookup and return arrays are different sizes, or if you forgot to lock your ranges with absolute references (e.g., $A$2:$A$100). Also, ensure your search_mode argument is correctly set to -1.
Can I get the first and last values without dynamic arrays?
Yes, you can use a combination of INDEX and MATCH. For the first value, a standard INDEX/MATCH works. For the last value, you can use an array formula like =INDEX(B:B, MAX(IF(A:A=G2, ROW(A:A)))) entered with Ctrl+Shift+Enter.
Does my data need to be sorted to use the UNIQUE and XLOOKUP method?
No. The XLOOKUP method with search modes 1 and -1 scans the original row order of your data. It will simply return the first match it physically encounters from the top, and the first match it physically encounters from the bottom, regardless of alphabetical sorting.
How do I deal with blank cells in my data?
If there are blank cells in your return array, XLOOKUP might return a 0 instead of a blank. You can append an empty string to the formula like =XLOOKUP(...)&"" to force it to return an empty text string for blanks.
