Function Problems

Excel Formula to Return the Latest Date with Numeric Data by Name

Emma BrownEmma Brown Nov 13, 2026 869 views

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.

Excel Formula to Return the Latest Date with Numeric Data by Name
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.
Before you start

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.

Solution 1Recommended

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.

1
Identify the source ranges

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.

2
Enter the lookup formula

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.

3
Display the result as a date

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.

4
Fill the formula down

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.

5
Extend the ranges when needed

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.

Use LOOKUP, INDEX, and MATCH to return the last date with numeric data
The last column is not always the latest date: This formula returns the rightmost numeric entry's date header, so dates must run from oldest to newest. Zero counts as numeric data, text dashes and numbers stored as text do not, and duplicate names match the first occurrence. A missing name or a matched row with no numbers can return #N/A.
Use WPS Spreadsheet

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. 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. 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. 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. 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.
Use LOOKUP, INDEX, and MATCH to return a date for each name.Work with Microsoft Excel .xlsx and .xls formats.Skip blank cells and text dashes when locating the last numeric entry.Fill the formula down to handle names listed in a different order.
microsoft office alternative - wps office

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.