How to Find the Highest Value Across Multiple Excel Sheets
Question details
The user needs to extract the maximum amount from a specific column across multiple monthly worksheets and retrieve the corresponding date from another column.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating and analyzing data across monthly divided worksheets to find the peak record.
- Observed behavior
- The user requires a dynamic formula to search, compare, and return the highest amount and its associated date from multiple sheets without manual checking.
Ensure that all your monthly worksheets (e.g., Jan through Dec) follow the exact same data structure and column layout before applying 3-D references.
Use LET, VSTACK, and SORT with a 3-D Reference
This is the most efficient method if your worksheets are contiguous and identically structured, allowing you to stack and sort the data in one formula.
This solution leverages a 3-D reference (e.g., Jan:Dec) to pull data from the same cell range across multiple consecutive worksheets.
It stacks the dates and amounts, sorts the combined array in descending order, and takes the top record.
Click on the cell in your summary sheet where you want the highest value and corresponding date to appear.
Type the following formula: =LET(dates,VSTACK(Jan:Dec!$A$2:$A$100),amounts,VSTACK(Jan:Dec!$E$2:$E$100),data,SORT(HSTACK(dates,amounts),2,-1),TAKE(data,1))
Press Enter. The formula will automatically spill the highest amount and its corresponding date into the adjacent cells.
Combine VSTACK, MAX, and XLOOKUP for Non-Contiguous Sheets
Ideal if you only need to check specific, non-contiguous sheets (e.g., Jan, Feb, Mar) instead of a continuous range.
Consolidate Data into a Single Master Sheet
If complex array formulas slow down your workbook, combining your data into a single master sheet makes analysis simpler.
Use WPS Spreadsheet for Advanced Array Functions
WPS Spreadsheet fully supports modern dynamic array functions like VSTACK, XLOOKUP, and LET, making it incredibly easy to analyze data across multiple worksheets.
- 1. Open your workbook: Launch WPS Spreadsheet and open your multi-sheet workbook.
- 2. Select a summary cell: Click on an empty cell in your summary sheet where you want the results to be displayed.
- 3. Enter the formula: Type the dynamic array formula using VSTACK and 3-D sheet references (e.g., Jan:Dec!A2:A100).
- 4. Calculate the result: Press Enter to instantly calculate and display the highest value across all sheets.
Frequently Asked Questions
What is a 3-D reference in Excel or WPS Spreadsheet?
A 3-D reference allows you to refer to the same cell or range of cells across multiple consecutive worksheets. For example, Jan:Dec!A1 refers to cell A1 in all sheets located between the 'Jan' and 'Dec' tabs.
Why does my VSTACK formula return a #NAME? error?
The #NAME? error usually occurs if your spreadsheet software version does not support dynamic array functions like VSTACK. Ensure you are using the latest version of WPS Office or Microsoft 365.
Can I find the lowest value instead of the highest?
Yes. If using the SORT function method, change the sort order argument from -1 (descending) to 1 (ascending), or use the MIN function instead of MAX in the XLOOKUP method.
What happens if multiple dates have the exact same highest amount?
The TAKE and XLOOKUP functions will generally return the first match they encounter in the stacked array. If you need all matching dates, you can use the FILTER function instead.
