How to Return the Date for the Maximum Value in an Excel 2D Array
Question details
Find the specific date (month and day) that corresponds to the largest value within a two-dimensional data array in Excel.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing a 2D dataset where columns represent days and rows represent months, and needing to pinpoint the exact date(s) when the maximum value occurred.
- Observed behavior
- The user needs a dynamic formula to compare all grid values against the maximum value, identify the matching row and column positions, and combine the headers into a valid date, while also handling ties where the max value appears multiple times.
Ensure your Excel version supports modern dynamic array functions like LET and TOCOL (available in Microsoft 365 and Excel 2021 or newer), and verify that your row and column headers contain valid month and day values.
Use a Dynamic Array Formula with LET and TOCOL
This is the most robust method for modern Excel versions. It searches the 2D grid for the maximum value and dynamically constructs the date, successfully returning multiple dates if there is a tie.
This formula defines variables for your data range, month headers, and day headers. It checks every cell against the overall MAX value. By using TOCOL, it extracts the corresponding row and column headers for any matches and combines them using the DATE function.
Locate your data grid (e.g., B5:AF16), your month headings (e.g., A5:A16), your day headings (e.g., B4:AF4), and the cell containing the year (e.g., A4).
Click on an empty cell where you want the resulting date to appear. Make sure there are empty cells below it in case the formula needs to return multiple dates.
Input the following formula: =LET(d,B5:AF16,m,A5:A16,y,A4,con,d=MAX(d),dates,TOCOL(IF(con,B4:AF4,""),2),months,TOCOL(IF(con,m,""),2),DATE(y,months,dates)) and press Enter.
Select the result cell(s), go to the Home tab, click the Number Format dropdown, and choose 'Short Date'.
Use SEQUENCE and INDEX to Extract the Date
An alternative dynamic approach that calculates the mathematical row and column indices of the maximum value to construct a text-based date string, which is then converted into a real date.
Analyze Complex 2D Arrays Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions, allowing you to effortlessly extract dates, find maximum values, and analyze complex 2D datasets. Handle your daily data operations efficiently with a familiar, powerful interface.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the 2D data array.
- 2. Apply the dynamic formula: Select an empty cell and input your dynamic array formula utilizing LET, TOCOL, and MAX to locate the maximum value's date.
- 3. Format and review results: Press Enter to calculate, then apply the Short Date format from the Home tab to view the extracted dates clearly.
Frequently Asked Questions
Why does my formula return a #NAME? error?
This error usually occurs if you are using an older version of Excel that does not support newer dynamic array functions like LET or TOCOL. You will need Microsoft 365, Excel 2021, or a compatible modern alternative like WPS Office to run these functions.
How can I handle multiple dates having the same maximum value?
By using dynamic array functions like TOCOL combined with an IF statement checking for the MAX value, the formula automatically returns an array of all matching dates. Ensure there are empty cells below your formula so the results can 'spill' downwards without encountering a #SPILL! error.
Can I use XLOOKUP for this 2D array search?
XLOOKUP is primarily designed for one-dimensional lookups (searching a single row or column). For a two-dimensional grid where the maximum value could be located anywhere, and where ties are possible, combining MAX with TOCOL, FILTER, or INDEX is much more effective.
Why is the result showing as a random 5-digit number instead of a date?
Excel stores dates as sequential serial numbers for calculation purposes. If your formula outputs a 5-digit number (like 45000), select the cell, go to the Home tab, and change the Number Format from 'General' to 'Short Date'.
