Function Problems

How to Return the Last Update Date from Status Values in Excel

Phi Hung VoPhi Hung Vo Nov 10, 2026 868 views

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.

How to Return the Last Update Date from Excel Status 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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target cell

Click on the cell where you want the latest matching date to be displayed.

2
Input the LOOKUP formula

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.

3
Apply and format

Press Enter to calculate the formula. Right-click the cell, select 'Format Cells', and choose a 'Date' format to display the result correctly.

Use the LOOKUP Function for a Reverse Search
Why this works: The LOOKUP(2, 1/(...)) structure forces the application to find the last valid numerical result in the array, effectively returning the latest date matching all your specified criteria.
Advanced Spreadsheet Tool

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. 1. Open your data file: Launch WPS Spreadsheet and open the document containing your status values and dates.
  2. 2. Select the destination cell: Click on the cell where you want to extract the last update date.
  3. 3. Enter the formula: Input your LOOKUP or XMATCH formula referencing the appropriate date and status ranges.
  4. 4. Format as Date: Press Enter, then use the Home tab to format the resulting number as a Short Date or Long Date.
Seamlessly handles complex array formulas and reverse LOOKUP functions.100% compatible with Microsoft Excel .xlsx and .xls formats.Free and lightweight with a familiar, easy-to-use tabbed interface.
microsoft office alternative - wps office

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)).