keepcalculator

06 Numbers and Powers

VLOOKUP Employee Info: How-To & Examples

Card No. 06·49510 · Updated · Standard formula, shown below

Yes, you can use VLOOKUP in Excel to retrieve employee information from a table by matching a unique identifier like an employee ID or email address. You enter the lookup value (e.g., the employee ID) and the function returns the corresponding detail from another column, such as name, department, or salary. This guide explains how to set it up, common errors, and practical examples.

How VLOOKUP Works

VLOOKUP stands for “Vertical Lookup.” It searches for a value in the first column of a table and returns a value from a specified column in the same row. The syntax is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • lookup_value: the value to search for (e.g., employee ID in cell A2).
  • table_array: the range of cells containing the data (e.g., A:D).
  • col_index_num: the column number in the table from which to return the value (1 for first column, 2 for second, etc.).
  • range_lookup: use FALSE for an exact match (recommended for employee IDs); TRUE for approximate match (rarely used with IDs).

For example, if your employee table is in columns A to D (ID, Name, Department, Salary), and you want to find the department of employee ID 104, you would use:

=VLOOKUP(104, A:D, 3, FALSE)

This returns the value from the third column (Department) for the row where column A equals 104.

Setting Up Your Employee Table

For VLOOKUP to work reliably, ensure your data is organized correctly:

  • The lookup column (e.g., Employee ID) must be the first column in the table array.
  • Each employee ID must be unique—duplicates cause VLOOKUP to return the first match only.
  • Keep the table range fixed when copying the formula by using absolute references (e.g., $A$2:$D$100).
  • Ensure the lookup value and the first column have the same data type (both numbers or both text).

If your employee data is on a separate sheet, include the sheet name in the table array, like Employees!$A$2:$D$100.

Step-by-Step Example

Suppose you have a sheet named “Employees” with columns:

  • A: Employee ID (numbers)
  • B: Full Name
  • C: Department
  • D: Annual Salary

On another sheet, you have a list of IDs in column A. In cell B2, you want the employee’s name. Enter:

=VLOOKUP(A2, Employees!$A$2:$D$100, 2, FALSE)

Drag the formula down to fill for other IDs. For department, change the column index to 3; for salary, use 4.

Common VLOOKUP Errors & Fixes

You may encounter these errors:

  • #N/A: The lookup value is not found. Check for typos, extra spaces, or mismatched data types. Use TRIM() to remove spaces.
  • #REF!: The column index is greater than the number of columns in the table array. Adjust the col_index_num.
  • #VALUE!: The lookup value is not in the first column of the table array, or the table array is not correctly defined.
  • Incorrect results: If using TRUE (approximate match), the first column must be sorted in ascending order. For employee IDs, always use FALSE to avoid wrong matches.

To handle #N/A gracefully, wrap the formula in IFERROR: =IFERROR(VLOOKUP(...), "Not Found").

VLOOKUP vs. INDEX/MATCH vs. XLOOKUP

VLOOKUP has limitations: it only looks to the right, and it breaks if you insert a column in the table. Alternatives include:

  • INDEX/MATCH: More flexible—can return values from any column, and the lookup column doesn’t have to be first. Example: =INDEX(Employees!B:B, MATCH(A2, Employees!A:A, 0)).
  • XLOOKUP (Excel 365/2021): Modern replacement, simpler syntax, and can search to the left. Example: =XLOOKUP(A2, Employees!A:A, Employees!B:B).

For most employee lookups, XLOOKUP is easier, but VLOOKUP remains widely used for compatibility.

Using VLOOKUP from Another Sheet or Workbook

To reference a different sheet, include the sheet name followed by an exclamation mark. For a different workbook, include the workbook name in square brackets, e.g., ='[HR Data.xlsx]Employees'!$A$2:$D$100. Note that the other workbook must be open in the same Excel instance for the formula to update live.

Frequently Asked Questions

Why does VLOOKUP return #N/A even though the ID exists?

This usually happens when the lookup value and the table column are different types (e.g., one is text and the other is number). Check if the ID in the table is stored as text (green triangle in cell). Convert both to the same type, or use =VLOOKUP(TEXT(A2,"0"), ...).

Can VLOOKUP return multiple columns at once?

No, VLOOKUP returns only one value. To get multiple columns, you need multiple formulas, or use an array formula like =VLOOKUP(A2, Table, {2,3,4}, FALSE) in Excel 365 (which spills results). Alternatively, use INDEX/MATCH or XLOOKUP.

How do I look up employee information from a dropdown list?

Create a data validation dropdown for employee IDs in a cell (e.g., A1). Then use that cell as the lookup value: =VLOOKUP(A1, Employees!A:D, 2, FALSE). When you change the dropdown selection, the formula updates automatically.

What if my employee IDs have leading zeros?

If IDs are stored as text (like “0012”), ensure both the lookup value and the table column are text. Format the lookup cell as text before entering the ID, or use =VLOOKUP(TEXT(A2,"0000"), ...) to match the format.

Does VLOOKUP work with employee names instead of IDs?

Yes, but names must be unique. If two employees share the same name, VLOOKUP returns the first match. It’s safer to use a unique ID or email.

How can I avoid #N/A when the employee is not in the list?

Use IFERROR to display a friendly message: =IFERROR(VLOOKUP(A2, Table, 2, FALSE), "Employee not found"). This is especially useful when you have many IDs to check.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →