Function Problems

Compare Two Excel Sheets and Return a Matching Email Address

Tauseeq MagsiTauseeq Magsi Nov 10, 2026 869 views

Question details

The user wants to compare names across two different worksheets based on first and last name columns and retrieve the associated email address into the first sheet.

How to Compare Two Excel Sheets and Return a Matching Email Address
Product
Spreadsheet
Device & OS
not provided
Scenario
Merging data from two separate lists where names are split into first and last name columns, to find corresponding contact information.
Observed behavior
The user needs a formula to cross-reference two criteria simultaneously and pull matching data into the target cell.
Before you start

Ensure that both worksheets have consistent column structures for first and last names. It is highly recommended to remove any extra leading or trailing spaces from the names using the TRIM function to avoid mismatch errors.

Solution 1Recommended

Use XLOOKUP to Match Multiple Criteria

The XLOOKUP function is a modern, powerful tool that easily handles multiple criteria by concatenating them with an ampersand (&).

By concatenating the First Name and Last Name columns in both the lookup value and the lookup array, you create a unique identifier to ensure the correct email address is returned.

1
Select Destination Cell

Open Sheet1 and click on the cell where you want the email address to appear (e.g., cell D2).

2
Enter XLOOKUP Formula

Type the formula `=XLOOKUP(A2&" "&B2,'Sheet2'!$A$2:$A$1000&" "&'Sheet2'!$B$2:$B$1000,'Sheet2'!$C$2:$C$1000,"")` into the formula bar.

3
Apply and Fill Down

Press Enter to apply the formula. Then, click and drag the fill handle (the small square at the bottom-right of the cell) down to copy the formula to the rest of the column.

Use XLOOKUP to Match Multiple Criteria
Formula Breakdown: A2&" "&B2 combines the first and last name from Sheet1. 'Sheet2'!$A$2:$A$1000&" "&'Sheet2'!$B$2:$B$1000 creates the lookup array by combining the names on Sheet2. 'Sheet2'!$C$2:$C$1000 is the return array containing the emails.
Seamless Data Processing

Use WPS Spreadsheet to Manage and Match Large Datasets Effectively

WPS Spreadsheet offers full support for advanced functions like XLOOKUP, VLOOKUP, and INDEX/MATCH, making it easy to compare sheets and consolidate data without lag.

  1. 1. Install WPS Office: Download and install WPS Office, then open your workbook in WPS Spreadsheet.
  2. 2. Navigate to Target Sheet: Go to your target sheet and select the cell for your email address formula.
  3. 3. Insert Formula: Use the Insert Function button (fx) to select XLOOKUP or type the formula directly into the formula bar.
  4. 4. Fill Data: Press Enter and double-click the fill handle to instantly populate the rest of your contact list.
Fully compatible with Microsoft Excel (.xlsx, .xls) file formats.Supports modern array formulas including XLOOKUP for multi-criteria matching.Lightweight and fast, even when processing thousands of rows of data.Free to use with a user-friendly, familiar tabbed interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why is my XLOOKUP formula returning an #N/A error?

An #N/A error means the exact combination of first and last name was not found in Sheet2. Check for extra spaces, spelling differences, or ensure that your lookup array range (e.g., $A$2:$A$1000) perfectly aligns in size with your return array range.

Can I match names if they are in a single column instead of two?

Yes. If full names are stored in a single column on both sheets, you do not need to use the ampersand (&) to combine columns. A standard XLOOKUP referencing the single name column will work perfectly.

Does the order of First Name and Last Name matter when comparing?

Yes, the order of concatenation matters. If your formula uses `A2&" "&B2` (First Name Space Last Name), the lookup array in Sheet2 must also be combined in the exact same order to find a match.

How do I compare sheets located in different workbooks?

You can reference another workbook by keeping it open while writing the formula. Click on the cells in the other workbook to let the program automatically generate the correct file path and sheet reference in your formula.