Function Problems

How to Automatically Return Employee Details from Another Excel Worksheet

Kushani Nimanthika Oct 12, 2026 869 views

Question details

The user needs a method to auto-fill employee details such as first name, last name, job title, and hire date on a template sheet by simply entering an Employee ID.

How to Automatically Return Employee Details from Another Excel Worksheet

Product: Excel
Device & OS: not provided
Scenario: Creating a dynamic employee information template that pulls data from a centralized master list worksheet automatically.
Observed behavior: The goal is to successfully construct a lookup formula that matches a unique Employee ID and retrieves the corresponding data fields accurately from a separate worksheet.

Before you start

Ensure your 'Masterlist' worksheet is organized with unique Employee IDs in the first column, and that you know the exact column numbers for the data you want to retrieve.

Solution 1Recommended

Use the VLOOKUP Function

VLOOKUP is the most direct and common method to search for a unique identifier in one sheet and return related information from another.

The VLOOKUP function requires four pieces of information: the value you want to look up, the range where the data is located, the column number in that range containing the value to return, and whether you want an exact match (FALSE).

Select the destination cell

Go to your Template worksheet and click on the cell where you want the first piece of information (e.g., First Name) to appear.

Input the VLOOKUP formula

Enter the formula =VLOOKUP($F$2,Masterlist!$A:$T,2,FALSE). In this example, $F$2 is the cell with the Employee ID, Masterlist!$A:$T is the data range on your master sheet, and 2 represents the second column.

Replicate for other fields

Repeat the formula in the other cells for Last Name, Job Title, etc., but change the column index number (e.g., from 2 to 3, 4, or 5) to match the location of the desired data in the Masterlist.

Exact Match Required: Always use FALSE as the last argument in your VLOOKUP formula to ensure Excel searches for an exact match of the Employee ID.

Solution 2

Use the INDEX and MATCH Functions

If your Employee ID column is not the very first column in your Masterlist, INDEX and MATCH provide a more flexible alternative to VLOOKUP.

Auto-Fill Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup functions like VLOOKUP, HLOOKUP, XLOOKUP, and INDEX/MATCH. You can easily cross-reference data across multiple worksheets to automate your templates and reports.

1. Open your workbooks: Launch WPS Spreadsheet and open the file containing your Masterlist and Template worksheets.
2. Access the Insert Function tool: Click on the target cell in your Template sheet, go to the 'Formulas' tab, and click 'Insert Function'.
3. Select VLOOKUP: Search for and select 'VLOOKUP', then use the dialog box to easily select your lookup value, table array, and column index without worrying about typing the syntax manually.
4. Apply and copy: Click OK to generate the result, then easily copy the formula to other cells using absolute references to fetch the rest of the employee details.

100% compatible with Microsoft Excel (.xlsx) file formatsSupports all standard and advanced lookup formulasLightweight, fast, and completely free to useIntuitive formula builder prevents syntax errors

microsoft office alternative - wps office

Frequently Asked Questions

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

The #N/A error usually means the Employee ID entered on the Template sheet cannot be found in the Masterlist. Check for typos, extra spaces, or ensure the ID actually exists. Also, verify that the lookup range in your formula is locked using absolute references (like $A$1:$T$100) if you are dragging the formula.

Can I use XLOOKUP instead of VLOOKUP?

Yes. If you are using a version of Excel or WPS Spreadsheet that supports XLOOKUP, it is highly recommended. XLOOKUP is more robust, defaults to exact matches, and can look in any direction. The syntax would be =XLOOKUP($F$2, Masterlist!$A:$A, Masterlist!$B:$B).

How do I stop my cell references from changing when I copy the formula?

You must use absolute cell references by adding dollar signs ($) before the column letters and row numbers (e.g., $F$2 instead of F2). You can quickly toggle this by pressing the F4 key on your keyboard while highlighting the cell reference in the formula bar.

Excel & Data Problems