Function Problems

How to Return a Matching Date from Another Column in Excel

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to retrieve matching capture dates into column B based on a subset of subject identifiers in column A, cross-referencing a master list located in columns D and E.

Product: Excel
Device & OS: not provided
Scenario: Cross-referencing a subset of identifiers against a master dataset to extract corresponding date values into a specific column.
Observed behavior: Looking for the correct lookup formula to accurately populate matching dates without shifting the reference range when copying the formula down.

Before you start

Ensure that your subject identifiers in both lists have identical formatting without hidden trailing spaces, and verify that the target column for dates is formatted as 'Date' to display correctly.

Solution 1Recommended

Use the XLOOKUP Function (Newer Excel Versions)

XLOOKUP is the modern, highly flexible lookup function in Excel that easily returns matching values and provides a built-in argument for handling missing data.

If you are using Microsoft 365 or a modern version of Excel, XLOOKUP is the recommended method. It processes arrays directly and eliminates the need for counting column index numbers.

Select the Target Cell

Click on cell B1, which is where you want the first matching capture date to be displayed.

Enter the XLOOKUP Formula

Type the formula =XLOOKUP(A1,D$1:D$1202,E$1:E$1202,"Not found") into the formula bar.

Apply and Fill Down

Press Enter to execute the formula. Then, click the small square at the bottom-right corner of cell B1 and drag it down to fill the formula for the rest of your subset list.

Absolute References: The dollar signs ($) in the formula lock the lookup arrays (D$1:D$1202 and E$1:E$1202), preventing the range from shifting downwards when you copy the formula.

Solution 2

Use the VLOOKUP Function (Older Excel Versions)

For users operating older versions of Excel that do not support XLOOKUP, the traditional VLOOKUP function is a reliable alternative.

Efficient Spreadsheet Alternative

Seamlessly Return Matching Dates with WPS Spreadsheet

WPS Spreadsheet natively supports advanced data functions like XLOOKUP and VLOOKUP, empowering you to cross-reference data and retrieve matching values efficiently with an interface identical to what you are used to.

1. Open Your Dataset: Launch WPS Spreadsheet and open the file containing your subject identifiers and capture dates.
2. Select the Output Cell: Click on the cell in column B where the corresponding date should appear.
3. Input the Lookup Formula: Type your preferred formula, for example =XLOOKUP(A1,D$1:D$1202,E$1:E$1202), into the formula bar.
4. Fill the Column: Hit Enter, then drag the fill handle downwards to quickly apply the data retrieval formula to all your subset identifiers.

Fully compatible with Microsoft Excel (.xlsx) files and formulas.Native support for complex functions including VLOOKUP, XLOOKUP, and INDEX/MATCH.Lightweight software architecture that handles large datasets without lagging.Free to use for essential data processing and analysis tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP or XLOOKUP formula returning a 5-digit number instead of a date?

Spreadsheet applications store dates as serial numbers. To display the result as a standard date, select the cells containing the 5-digit numbers, right-click, choose 'Format Cells', and select your preferred 'Date' format.

How do I prevent my lookup range from moving when I copy the formula down?

You need to use absolute references. By adding a dollar sign ($) before the row numbers (e.g., D$1:D$1202), you lock the reference range so it remains static when the formula is dragged down.

What should I do if VLOOKUP returns an #N/A error?

An #N/A error means an exact match for your identifier was not found in the source column. Verify that there are no extra spaces or typos in the identifiers. If the value genuinely doesn't exist, you can wrap the formula in an IFERROR function, like =IFERROR(VLOOKUP(...), "Not found").

Can I use columns from a completely different workbook for my lookup?

Yes, both XLOOKUP and VLOOKUP allow you to reference ranges in other open workbooks. However, keep both workbooks open to ensure the links update quickly without errors.

Excel & Data Problems