Compare Two Excel Sheets and Return a Matching Email Address
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.

- 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.
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.
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.
Open Sheet1 and click on the cell where you want the email address to appear (e.g., cell D2).
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.
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 INDEX and MATCH for Older Spreadsheet Versions
If you are using a version of Excel or a spreadsheet program that does not support XLOOKUP, you can use the classic INDEX and MATCH combination.
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. Install WPS Office: Download and install WPS Office, then open your workbook in WPS Spreadsheet.
- 2. Navigate to Target Sheet: Go to your target sheet and select the cell for your email address formula.
- 3. Insert Formula: Use the Insert Function button (fx) to select XLOOKUP or type the formula directly into the formula bar.
- 4. Fill Data: Press Enter and double-click the fill handle to instantly populate the rest of your contact list.

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.




