How to Find the Latest Date by Ticket Number in Excel
Question details
The user needs an Excel formula to match a specific ticket number across tables and return the maximum (latest) date from horizontally arranged columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing client tickets and trying to extract the most recent action date from horizontally structured data across multiple sheets.
- Observed behavior
- The user needs to retrieve the latest date for a matched ticket number, but the dates are spread horizontally across multiple columns instead of vertically, requiring a specialized array lookup.
Ensure that your source data is formatted as an Excel Table (using Ctrl+T) so you can use structured references, and verify that your horizontal date columns are formatted as Dates rather than plain text.
Use MAX, INDEX, and MATCH Formulas
Combine the MAX, INDEX, and MATCH functions to look up the ticket number and evaluate the array of horizontal dates to extract the most recent one.
This structured-reference formula works by first locating the exact row of your ticket number using MATCH. Then, INDEX retrieves all the horizontal cells in that specific row. Finally, MAX evaluates those horizontal cells to return the highest numerical value, which represents the latest date.
Highlight your data ranges and press Ctrl+T to convert them into Tables. Note the names of your tables and columns (e.g., Table2 for the source data, Columns1 for the ticket number column, and Columns2 through Columns6 for the horizontal dates).
Click the cell where you want the latest date to appear. Type the MATCH function to find the row index: MATCH([@Column1], Table2[Columns1], 0).
Enclose the MATCH function inside an INDEX function to retrieve the row's date array. Your formula should now look like: INDEX(Table2[[Columns2]:[Columns6]], MATCH([@Column1], Table2[Columns1], 0), ). Notice the comma at the end before the closing parenthesis, which tells INDEX to return the entire row of columns 2 through 6.
Wrap the entire formula in the MAX function to pull the highest date value. The final formula is: =MAX(INDEX(Table2[[Columns2]:[Columns6]],MATCH([@Column1],Table2[Columns1],0),)). Press Enter to apply it.
Easily Manage and Analyze Ticket Dates in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas like MAX, INDEX, and MATCH, allowing you to seamlessly look up and extract horizontal dates with high performance.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the horizontal ticket tables.
- 2. Enter the Lookup Formula: Click the target cell and input the combined formula: =MAX(INDEX(Table2[[Columns2]:[Columns6]],MATCH([@Column1],Table2[Columns1],0),)).
- 3. Format the Output: Press Enter to calculate. Go to the Home tab, click the Number Format dropdown, and select Short Date to display the result properly.
Frequently Asked Questions
Why is my formula returning a number like 44500 instead of a date?
Spreadsheet programs store dates as sequential serial numbers for calculation purposes. To fix this, simply select the output cell, navigate to the Home tab, and change the number format from 'General' to 'Short Date'.
Can I use XLOOKUP instead of INDEX and MATCH for horizontal dates?
Yes, if you are using a newer version of Excel or WPS Spreadsheet that supports dynamic arrays, you can use the XLOOKUP function. The formula would look like: =MAX(XLOOKUP([@Column1], Table2[Columns1], Table2[[Columns2]:[Columns6]])).
What if there are blank cells in the horizontal date columns?
The MAX function automatically ignores blank cells and text values when evaluating the array of dates returned by the INDEX function. As long as there is at least one valid date in the row, the formula will accurately find the latest date.
