Function Problems

How to Cross-Reference Two Excel Sheets and Return a Matching Date

Maira Mehtab Sep 21, 2026 871 views

Question details

The user needs to cross-reference data between two Excel sheets using multiple identifying fields (such as surname, given name, and date of birth) to return a specific date from the first sheet, or an 'X' if no match is found.

Product: Spreadsheet
Device & OS: not provided
Scenario: Matching records across multiple worksheets based on composite keys to retrieve associated date values without altering the original source data.
Observed behavior: The user expects a formula that evaluates all identifying fields simultaneously and returns either the target date or a default 'X' value for unmatched rows.

Before you start

Ensure both sheets are within the same workbook and that the data types in your identifying columns match exactly across both sheets to prevent formula errors.

Solution 1Recommended

Use XLOOKUP with Concatenated Fields

Combine multiple criteria into a single lookup string using the ampersand (&) operator within the XLOOKUP function. This is the most straightforward method for modern spreadsheet versions.

XLOOKUP allows you to search for multiple criteria simultaneously by concatenating the lookup values and the lookup arrays. This eliminates the need for complex array formulas or helper columns.

Select the target cell

Navigate to Sheet2 and click on the cell where you want the matching date to appear (for example, cell D2).

Enter the XLOOKUP formula

Type the formula: =XLOOKUP(A2&B2,'Sheet1'!$A$2:$A$15&'Sheet1'!$B$2:$B$15,'Sheet1'!$D$2:$D$15,"X"). If you need to evaluate all three fields (surname, given name, date of birth), expand it to: =XLOOKUP(A2&B2&C2,'Sheet1'!$A$2:$A$15&'Sheet1'!$B$2:$B$15&'Sheet1'!$C$2:$C$15,'Sheet1'!$D$2:$D$15,"X").

Apply to the remaining rows

Press Enter to execute the formula. Click the small square at the bottom-right corner of the cell and drag it down to apply the formula to the rest of your data.

Absolute References: Make sure to use absolute references (the $ signs) for your source arrays in Sheet1 so the ranges do not shift when you drag the formula down.

Solution 2

Use INDEX and XMATCH for Complex Array Matching

Utilize INDEX combined with XMATCH and boolean logic to evaluate multiple columns independently. This is highly reliable for large datasets with complex criteria.

WPS Spreadsheet Solution

Easily Cross-Reference Sheets with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like XLOOKUP, INDEX, and XMATCH. You can effortlessly manage large datasets, cross-reference multiple sheets, and evaluate complex array formulas in a familiar, intuitive interface.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your two sheets.
2. Navigate to the target sheet: Switch to Sheet2 and select the cell where you want to retrieve the matched dates.
3. Insert the XLOOKUP function: Click the 'Formulas' tab and select 'Insert Function' to find XLOOKUP, or type it directly into the formula bar using the concatenation method.
4. Calculate and fill: Press Enter to calculate the result, then use the fill handle to apply the formula across your entire dataset.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Supports advanced dynamic array functions like XLOOKUP natively.Lightweight software ensuring fast processing speeds for large datasets.User-friendly interface perfect for complex data management tasks.

Frequently Asked Questions

Why does my XLOOKUP formula return an error instead of the matching date?

This usually happens if the lookup arrays are different sizes or if there are trailing spaces in your data. Ensure your ranges match exactly (e.g., $A$2:$A$15 and $D$2:$D$15) and consider using the TRIM function to clean your text data.

How can I format the result as a date instead of a serial number?

If your formula returns a number like 44200, select the cells, right-click, choose 'Format Cells', and select the 'Date' format from the Number tab to display it correctly.

Can I use VLOOKUP instead of XLOOKUP for multiple criteria?

VLOOKUP does not natively support multiple criteria unless you create a helper column in the source sheet that concatenates the identifying fields. XLOOKUP or INDEX/MATCH are much better suited for multi-condition lookups because they do not require altering the source data.

Why use array multiplication in the INDEX/XMATCH formula?

Array multiplication acts as a logical AND operator. It checks each condition, returning 1 (True) or 0 (False). When all conditions are True for a specific row, it multiplies to 1, which the XMATCH function then locates.

Excel & Data Problems