How to Return the Date Associated with the Highest Number in Excel
Question details
Retrieve a date from a specified range that corresponds to the highest numeric value located in an adjacent or designated lookup range.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- The user has a row of data with values and dates, and needs to automatically extract the specific date that aligns with the maximum peak value in that row.
- Observed behavior
- The user wants to establish a formula that accurately maps the highest identified value to its corresponding date without manual searching.
Ensure that your lookup range (the numbers) and your return range (the dates) are of the exact same size and properly aligned to avoid #N/A or #VALUE! errors.
Use INDEX and XMATCH Functions
Combine the INDEX and XMATCH functions to search for a specific maximum value and return the corresponding date from an offset range.
The XMATCH function is a modern successor to MATCH that defaults to exact matches. When nested inside an INDEX function, it easily locates the relative position of the highest number, allowing INDEX to pull the date from the same position in your date range.
Click on the cell where you want the corresponding date to appear (for example, cell AT3).
Type the formula =INDEX(F3:AP3, XMATCH(AS3, E3:AO3)). In this formula, AS3 contains the highest number you are looking for, E3:AO3 is the range containing the numbers, and F3:AP3 is the range containing the dates.
Press Enter to apply the formula. If you have multiple rows, click and drag the fill handle at the bottom-right corner of the cell to copy the formula down the column.

Use the XLOOKUP Function
Utilize Excel's XLOOKUP function as a straightforward, single-formula alternative to search for a value and return an adjacent result.
Easily Extract Complex Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced lookup functions like INDEX, MATCH, and XLOOKUP, allowing you to easily find and extract data based on specific criteria. It is a highly compatible and lightweight solution for your everyday data analysis.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your numbers and dates.
- 2. Start your formula: Select an empty cell and type =XLOOKUP( or =INDEX( depending on your preferred method.
- 3. Select your ranges: Highlight your lookup value (the highest number), followed by your lookup range, and finally the date return range.
- 4. Get your result: Press Enter to instantly view the corresponding date. Use the fill handle to apply it to other rows.

Frequently Asked Questions
Why does my lookup formula return an #N/A error?
An #N/A error generally means the lookup value (the highest number) was not found in the lookup range. It can also occur if there is a mismatch in the dimensions of your lookup and return ranges, or if there are invisible spaces in your data.
Can I use VLOOKUP instead of XLOOKUP or INDEX/MATCH for this task?
VLOOKUP is designed to search only the leftmost column of a range and return a value to the right. If your dates are located to the left of your numbers, or if you are searching across horizontal rows (which requires HLOOKUP), INDEX/MATCH or XLOOKUP are much more flexible and highly recommended.
How do I find the highest number automatically within the formula itself?
You can nest the MAX function directly inside your lookup formula. For instance, using =XLOOKUP(MAX(E3:AO3), E3:AO3, F3:AP3) will automatically identify the highest number in the range and return its corresponding date in one step.




