How to Return the Last Update Date from Status Values in Excel
Question details
The user needs to retrieve the most recent update date from a multi-row range based on specific status conditions such as status codes, numeric flags, and TRUE values.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking project updates or data logs where the user must identify the latest modification date that matches a combination of specific criteria.
- Observed behavior
- The goal is to accurately fetch the last matching date either at a row-level or across the entire dataset using combined conditional flags.
Ensure your date columns are properly formatted as Dates rather than Text, and verify that the status columns do not contain hidden trailing spaces that could interfere with exact formula matching.
Use the LOOKUP Function for a Reverse Search
The classic LOOKUP function is highly effective for finding the last instance of a value because it inherently searches an array from the end to the beginning.
By dividing 1 by a set of Boolean conditions, we force the formula to generate an array of 1s and #DIV/0! errors. LOOKUP then searches for the number 2, ignores the errors, and matches the last valid 1 in the array.
Click on the cell where you want the latest matching date to be displayed.
Type a formula combining your conditions, for example: =LOOKUP(2, 1/((B2:B20="P")*(A2:A20=TRUE)), C2:C20) where column C contains your dates.
Press Enter to calculate the formula. Right-click the cell, select 'Format Cells', and choose a 'Date' format to display the result correctly.

Use XMATCH and INDEX for Advanced Reverse Searching
If you are using a newer version of the software that supports dynamic arrays, XMATCH offers a built-in argument specifically designed to search from last to first.
Find the Last Update Date Easily with WPS Spreadsheet
WPS Spreadsheet offers powerful lookup functions and dynamic array support, making it simple to retrieve the latest dates based on complex status codes. It seamlessly handles advanced formulas to streamline your data analysis.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing your status values and dates.
- 2. Select the destination cell: Click on the cell where you want to extract the last update date.
- 3. Enter the formula: Input your LOOKUP or XMATCH formula referencing the appropriate date and status ranges.
- 4. Format as Date: Press Enter, then use the Home tab to format the resulting number as a Short Date or Long Date.

Frequently Asked Questions
Why does my formula return an error instead of the last date?
This usually happens if the array sizes in your formula do not match perfectly. Ensure your date range, status range, and flag range all contain the exact same number of rows (e.g., A2:A20, B2:B20, C2:C20).
Can I return dates for each row individually instead of one single date?
Yes. You can adjust the formula to use relative row references instead of absolute ranges ($A$2:$A$20). This allows you to drag the formula down the column to calculate the last update date on a per-row basis.
How do I check for multiple status codes like 'P', 'C', and numbers 1 to 5?
You can expand your Boolean logic inside the formula by adding the conditions together with a plus sign, which acts as an 'OR' operator. For example: ((StatusRange="P")+(StatusRange="C")+(StatusRange>=1)*(StatusRange<=5)).




