Function Problems

How to Return a Date Based on Two Criteria in Excel

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to retrieve a specific date from a dataset on another worksheet by matching two conditions: a plot number and a flag stage.

Product: Excel
Device & OS: not provided
Scenario: Extracting a target date from a data table when two specific criteria are met across different columns.
Observed behavior: A single formatted date value is successfully returned from the source table to the destination cell while suppressing blank results.

Before you start

Ensure your source data is formatted as an Excel Table for easier column referencing, and verify that your target cells are configured to display dates correctly.

Solution 1Recommended

Use the FILTER Function with Multiple Criteria

The FILTER function is the most efficient and modern way to extract data based on multiple conditions using standard multiplication for AND logic.

The FILTER function can seamlessly handle multiple criteria by multiplying the condition arrays together. This operation effectively acts as an 'AND' logic gate, ensuring only rows that meet both criteria are returned.

Select the destination cell

Click on the cell in your destination worksheet where you want the returned date to appear.

Input the FILTER formula

Enter the formula: =FILTER(Table1[[Start]:[Start]],(Table1[[Plot No]:[Plot No]]=$F10)*(Table1[[Flag Stages]:[Flag Stages]]=G$3),""). Make sure to adjust the table names and cell references to match your actual data.

Format the output as a date

Right-click the result cell, select 'Format Cells', choose the 'Custom' category, and enter dd/mm/yyyy;; in the type box. This will correctly display the date and suppress any zero or blank results.

Formula Customization: You can change the empty string "" at the end of the FILTER formula to "Not Found" if you prefer to see a specific text prompt when no matching date exists.

Advanced Spreadsheet Tool

Easily Extract Data with Multiple Criteria in WPS Office

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER, making it incredibly simple to retrieve dates and other specific data based on multiple conditions across your worksheets.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your spreadsheet file containing the source data tables.
2. Apply the FILTER formula: Type your FILTER formula in the target cell, using the multiplication symbol (*) to combine your multiple criteria arrays seamlessly.
3. Format the cell for dates: Use the keyboard shortcut Ctrl+1 to open the Format Cells dialog, select Custom, and apply your preferred date format to hide blanks.

Fully compatible with Microsoft Excel formulas and functionsNative support for modern dynamic array functions including FILTER, UNIQUE, and SORTLightweight, fast, and free to use for your daily spreadsheet analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does my FILTER function return a #CALC! error?

The #CALC! error typically occurs when the FILTER function cannot find any data that matches all of your criteria. You can prevent this error from displaying by providing a value (such as "" or "Not Found") in the third argument [if_empty] of the FILTER function.

Can I use INDEX and MATCH instead of FILTER for multiple criteria?

Yes. If you are using an older version of Excel that does not support the dynamic FILTER function, you can use an array formula: =INDEX(ReturnRange, MATCH(1, (Criteria1Range=Condition1)*(Criteria2Range=Condition2), 0)). Remember to press Ctrl+Shift+Enter to evaluate it as an array formula in older versions.

How do I format a date cell to hide zero values or blanks in Excel?

In the Format Cells dialog under Custom formatting, you can use a format code like dd/mm/yyyy;; to hide zeros. The semicolons separate the formats for positive numbers, negative numbers, and zeros. Omitting the format rule for zeros keeps them hidden.

Excel & Data Problems