Function Problems

How to Match Employee IDs and Return Email Addresses in Excel

Huda Qurayshi Oct 1, 2026 868 views

Question details

The user needs to retrieve email addresses from another spreadsheet by matching Employee IDs, but is encountering formula errors such as #SPILL!.

How to Match Employee IDs and Return Email Addresses in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating employee contact information by cross-referencing an Employee ID across different sheets or workbooks.
Observed behavior: The lookup formula fails to return the email address, occasionally displaying a #SPILL! error due to incorrect ranges or blocked destination cells.

Before you start

Ensure both the source and destination workbooks are open, and verify that the Employee ID columns in both sheets share the exact same format (e.g., both formatted as Text or both as Numbers) to prevent mismatch errors.

Solution 1Recommended

Use VLOOKUP with Exact Match

The VLOOKUP function is the standard method for searching a unique identifier (like an Employee ID) in one sheet and returning corresponding data (like an email address) from an adjacent column.

For VLOOKUP to work correctly, the Employee ID must be in the first column of your selected lookup range, and the email addresses must be in a column to the right.

Select the destination cell

Click on the cell where you want the retrieved email address to appear (for example, cell B2).

Enter the VLOOKUP formula

Type =VLOOKUP(A2, Sheet1!$A$2:$B$21, 2, FALSE). In this formula, A2 is the Employee ID you are searching for, Sheet1!$A$2:$B$21 is the data range containing both IDs and emails, and 2 indicates that emails are in the second column of that range.

Use absolute references

Ensure you use the $ symbols for your lookup range (e.g., $A$2:$B$21) so the range does not shift when you copy the formula down.

Apply formula to other cells

Press Enter, then click the small square at the bottom-right corner of the cell and drag it down to apply the formula to the rest of the employee list.

Keep Workbooks Open: If you are referencing a completely different workbook, keep both files open while selecting ranges. Excel will automatically add the workbook name to the formula.

Solution 2

Resolve the #SPILL! Error

If you are using modern dynamic array formulas (like taking advantage of Office 365's spilling behavior) and you encounter a #SPILL! error, it means the formula doesn't have enough empty space to display all results.

Powerful Spreadsheet Software

Match Employee Data Seamlessly with WPS Spreadsheet

Easily manage HR records, write VLOOKUP formulas, and avoid calculation errors with WPS Office. It provides a familiar interface and comprehensive function support for all your data management needs.

1. Open your files in WPS Spreadsheet: Launch WPS Office and open both the destination workbook and the source workbook containing your employee contact list.
2. Insert the VLOOKUP function: Select the empty cell for the email address, go to the Formula tab, and click 'Insert Function' to search for VLOOKUP, or type =VLOOKUP( directly.
3. Select your lookup range: Click the Employee ID cell, type a comma, then switch to the source workbook. Highlight the columns containing the IDs and Emails.
4. Complete the formula: Press F4 to lock the data range, type ', 2, FALSE)' to specify the email column and require an exact match, then press Enter.

100% compatible with Microsoft Excel (.xlsx) formatsFull support for VLOOKUP, XLOOKUP, and dynamic arraysBuilt-in error checking to help fix #N/A and #VALUE! issues instantlyFree, lightweight, and perfect for daily office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP formula return an #N/A error?

The #N/A error means Excel cannot find the Employee ID in the source data. This usually happens if the ID does not exist, has trailing spaces, or if one sheet stores the ID as a number while the other stores it as text.

Can I use XLOOKUP instead of VLOOKUP to find email addresses?

Yes. XLOOKUP is often easier because it defaults to an exact match and allows the Employee ID column to be anywhere in your data, not just the first column. The syntax is =XLOOKUP(A2, Source_ID_Range, Source_Email_Range).

What does the 'FALSE' argument do in the VLOOKUP formula?

The 'FALSE' argument tells the function to look for an exact match of the Employee ID. If you omit it or use 'TRUE', Excel might return an approximate match, resulting in the wrong email address being assigned to an employee.

Excel & Data Problems