Function Problems

How to Use an Excel Formula to Find a Date and Return a Value

WPS Content Manager Sep 28, 2026 869 views

Question details

The user wants to look up a specific date on another worksheet and return the adjacent value, while handling errors by returning zero if the date is not found.

Excel Formula to Find a Date and Return the Value Beside It

Product: Excel
Device & OS: not provided
Scenario: Creating a summary sheet that pulls data from another worksheet based on an input date without showing #N/A errors for missing dates.
Observed behavior: Needs a formula that retrieves the corresponding value for a matched date, returning zero instead of an error when the target date is missing.

Before you start

Ensure that the dates in your lookup column are formatted correctly as Dates, and that your target return values are positioned in a column to the right of your lookup dates.

Solution 1Recommended

Use VLOOKUP and IFERROR to Retrieve the Value

The VLOOKUP function is the most straightforward way to search for a date and return an adjacent value, combined with IFERROR to output a zero instead of an error.

The VLOOKUP function searches for a value in the first column of an array and returns a value in the same row from another column. By wrapping it in IFERROR, you can cleanly handle instances where the lookup date is missing from your source data.

Identify the lookup value

In your summary sheet (e.g., Sheet2), select the cell where you want to input the date you are searching for (e.g., cell A1).

Enter the formula

Click on the result cell (e.g., B1) and type the following formula: =IFERROR(VLOOKUP(A1,Sheet1!A:B,2,FALSE),0)

Understand the syntax

In this formula, 'A1' is the date to find, 'Sheet1!A:B' is the search range on the other sheet, '2' returns the value from the second column, and 'FALSE' ensures an exact match.

Apply the formula

Press the Enter key. The cell will now display the value next to the date from Sheet1, or a 0 if the date does not exist.

Use VLOOKUP and IFERROR to Retrieve the Value

Formatting Tip: Make sure the cell containing the formula result is formatted as a Number or General so the '0' displays correctly instead of appearing as a random date.

Smart Spreadsheet Solution

Easily Look Up Data and Handle Errors with WPS Spreadsheet

WPS Spreadsheet fully supports VLOOKUP, IFERROR, and all standard Excel functions. You can seamlessly manage your summary sheets, pull data across multiple worksheets, and handle missing values effortlessly.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the data and summary sheets.
2. Enter the lookup formula: Select your target cell in the summary sheet and input the formula =IFERROR(VLOOKUP(A1,Sheet1!A:B,2,FALSE),0).
3. View your results: Press Enter to instantly fetch the corresponding data or display a zero for unmatched dates.

Fully compatible with Microsoft Excel formulas and .xlsx files.Advanced data lookup and summary tools.Lightweight, free to use, and runs smoothly on all devices.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use XLOOKUP instead to find the date?

Yes. If you are using a version of Excel or WPS Spreadsheet that supports XLOOKUP, you can use =XLOOKUP(A1, Sheet1!A:A, Sheet1!B:B, 0) to achieve the same result without needing the IFERROR function.

Why is my VLOOKUP returning an error even when the date is visibly there?

This usually happens due to mismatched cell formats. Ensure that both the lookup cell and the date column in your source sheet are formatted exactly as 'Date', and contain no hidden spaces or time values.

How do I return a blank cell instead of a zero when a date is missing?

To return a blank cell when no match is found, replace the 0 at the end of the IFERROR formula with double quotes. The updated formula will look like this: =IFERROR(VLOOKUP(A1,Sheet1!A:B,2,FALSE),"").

Excel & Data Problems