Excel Formula to Return the Latest Date with Numeric Data by Name
Question details
Return the latest date whose column contains a numeric value for each name, even when names appear in a different order in the results table.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A source worksheet lists names in rows, date headers in columns, and numbers, text dashes, or blanks in the data cells. A results table needs the corresponding latest date for each name as the source data changes.
- Observed behavior
- The desired result is the date header above the last numeric entry in the matched name's row, ignoring blanks and text dashes.
Confirm that source names are in Data!A2:A2000, date headers are in B1:Z1, and values are in B2:Z2000, or adjust these ranges to match your worksheet. Arrange date headers from oldest to newest and check that values intended to count are stored as numbers rather than text.
Use LOOKUP, INDEX, and MATCH to return the last date with numeric data
Use this formula when each name identifies one source row and date columns run from oldest to newest.
MATCH finds the source row for the name, INDEX returns that row's values, and LOOKUP returns the date header corresponding to its last numeric entry. The large lookup value, 9.99999999999999E+307, is used to find the final number in the row while ignoring text and blanks.
Open the Data worksheet and locate the names, date headers, and numeric data. In this example, names occupy A2:A2000, headers occupy B1:Z1, and data occupies B2:Z2000.
In the results worksheet, put a name in A2, select the result cell beside it, and enter =LOOKUP(9.99999999999999E+307,INDEX(Data!$B$2:$Z$2000,MATCH(A2,Data!$A$2:$A$2000,0),0),Data!$B$1:$Z$1). Press Enter.
Select the result cell, open Home > Number Format, and choose Short Date or Long Date. If the date headers are actual Excel dates, this changes a displayed serial number into a readable date.
Drag the result cell's fill handle down beside the remaining names. A2 changes for each results row, while the dollar signs keep the source ranges fixed.
When adding source rows beyond row 2000, update both the name range and data range. When adding date columns beyond Z, extend both the data range and date-header range to the same ending column.

Find Each Name's Latest Numeric Entry Date in WPS Spreadsheet
Open the workbook in WPS Spreadsheet and use the same LOOKUP, INDEX, and MATCH formula to match each name and return the date above its last numeric entry.
- 1. Open the source workbook: Launch WPS Spreadsheet and choose Open to load your Excel workbook. Confirm the source sheet name and the ranges containing names, dates, and values.
- 2. Enter the formula beside a name: Select the result cell beside the name in A2 and enter =LOOKUP(9.99999999999999E+307,INDEX(Data!$B$2:$Z$2000,MATCH(A2,Data!$A$2:$A$2000,0),0),Data!$B$1:$Z$1), adjusting the sheet name and ranges as needed.
- 3. Apply date formatting and fill down: Select the result cell, use Home > Number Format to choose a date format, and drag the fill handle down to calculate results for the remaining names.
- 4. Save the workbook: Choose Menu > Save As and select .xlsx to keep the workbook in Microsoft Excel format. Extend the formula ranges if future data goes beyond the specified rows or columns.

Frequently Asked Questions
Will this work if the names are in a different order in my results table?
Yes. MATCH(A2,Data!$A$2:$A$2000,0) finds an exact match for the name in A2, so the results table does not need to follow the source row order. If the source contains duplicate names, MATCH uses the first matching row.
Do zeros, dashes, and blank cells count as data?
Zero is a number and counts as data. Empty cells and text dashes are ignored. A numeric zero displayed as a dash through number formatting still counts because the underlying value is numeric.
Why does the formula return #N/A?
Check whether the name in A2 exists in Data!A2:A2000 and whether its matched row contains any actual numbers. Extra spaces in names or numbers stored as text can prevent the expected result. To display a blank instead of an error, wrap the complete formula in IFNA(your_formula,"").
Why is the result a number instead of a date?
Excel stores dates as serial numbers. Select the result cell and choose Home > Number Format > Short Date. Also confirm that the source date headers are actual dates rather than text labels.
Will the formula include new rows and date columns automatically?
It recalculates when values change within the referenced ranges, assuming automatic calculation is enabled. New rows beyond 2000 or columns beyond Z are not included until you edit the formula to expand the matching source ranges. Keep date headers in chronological order so the rightmost numeric entry represents the latest date.




