Build an Employee Lookup Sheet in Excel

An employee lookup sheet in Excel lets anyone type an employee ID and instantly see that person's name, department, location and phone extension. You build it with one staff table and a small "lookup card" of XLOOKUP formulas, plus a friendly "Not found" message for wrong IDs. This project is useful practice for HR and admin roles, and it covers the lookup skills most Excel tests check.

What you're building#

The finished sheet has two parts side by side: the staff table (the data) and the lookup card (the formulas). You only ever type in one cell of the card, the ID.

Here is the staff table:

Two rules keep the lookup reliable. Every ID must be unique, and the table should have no blank rows or merged cells.

Step 1: Build the lookup card with XLOOKUP#

Each field on the card is one XLOOKUP that searches the ID column and brings back a different column. XLOOKUP's shape is =XLOOKUP(what to find, where to look, what to bring back, [if not found]).

Put the labels in column G and the formulas in column H:

The $ signs lock the ID cell and the table, so you can copy H2 down and only change the return column. Press F4 on Windows or ⌘T on a Mac to add them. The XLOOKUP function guide explains every argument, and XLOOKUP in Excel for beginners has more examples.

Get the whole card from one formula#

XLOOKUP can return several columns at once. =XLOOKUP(H1,A2:A7,B2:E7) spills (fills) the name, department, location and extension across one row. To show them down a column like a card, wrap it in TRANSPOSE:

=TRANSPOSE(XLOOKUP(H1,A2:A7,B2:E7))

Leave the cells below empty, or Excel shows #SPILL!. The one-formula version is neat, but separate formulas are easier for colleagues to read and edit.

Step 2: Handle IDs that don't exist#

When an ID isn't in the table, XLOOKUP returns #N/A unless you give it a fourth argument. With "Not found" in place, typing E-199 shows Not found in every field.

If a real ID still shows Not found, the data is usually the problem:

• Extra spaces: "E-104 " with a trailing space is a different value. Clean it with TRIM; see clean messy data with TRIM, CLEAN and PROPER.
• Numbers vs text: if IDs are plain numbers, one list may hold them as text. See numbers stored as text.
• Wrong range: the ID column must cover every row of the table.

Our guide to fixing #N/A errors in lookups goes through each cause.

Step 3: Make the ID a drop-down list#

A drop-down list stops typos, because people pick an ID instead of typing it.

1. Select H1.
2. Go to Data > Data Validation (Windows shortcut: Alt, A, V, V). On a Mac, use Data > Data Validation.
3. Under Allow, choose List.
4. In Source, type =$A$2:$A$7, then click OK.

Create a drop-down list in Excel covers lists that grow as you add staff.

Step 4: Add search by name and by department#

The same table answers other questions with small changes.

Find an ID from a name. The ID column is to the left of the names, which VLOOKUP can't do but XLOOKUP can. With Lena Ortiz in H8:

=XLOOKUP(H8,B2:B7,A2:A7)

The result is E-105. More ways to do this are in how to look up to the left.

Search by part of a name. Match mode 2 allows wildcards. With Khan in H15, this returns E-103:

=XLOOKUP("*"&H15&"*",B2:B7,A2:A7,"Not found",2)

List everyone in a department. XLOOKUP stops at the first match, so use FILTER to get them all. With Sales in H11:

=FILTER(B2:B7,C2:C7=H11)
=COUNTIFS(C2:C7,H11)

FILTER spills Tom Becker and Mateo Silva, and COUNTIFS returns 2. Our comparison of XLOOKUP vs FILTER explains when to use each.

Version note: building it in older Excel#

XLOOKUP and FILTER need Excel 2021, Excel 2024, Microsoft 365 or Excel for the web. In Excel 2019 or older, use VLOOKUP with an exact match inside IFERROR:

=IFERROR(VLOOKUP($H$1,$A$2:$E$7,2,FALSE),"Not found")

Change the column number (2) for each field: 3 for Department, 4 for Location, 5 for Extension. VLOOKUP can't look left, so "find the ID from a name" needs INDEX and MATCH there. VLOOKUP vs XLOOKUP compares the two in detail.

How to extend the employee lookup sheet#

Once the card works, a few additions make it a real tool:

1. Put the table on its own sheet and the card on a "Lookup" sheet. Reference it like Staff!$A$2:$A$7; see reference another sheet.
2. Turn the staff list into a table (Ctrl+T on Windows, or Insert > Table) so new hires are included automatically.
3. Protect the formulas so only H1 can be edited: protect a sheet and lock cells.
4. Add start dates and calculate years of service with DATEDIF.
5. Add a schedule or price list next: the weekly shift schedule project and the price list with discounts and tax use the same "table plus formulas" pattern.

This exact task, finding a person's details by ID and counting a department, appears often in hiring tests. See how to pass an Excel test for a job interview, the Excel interview questions you can practise, and the Excel skills every data analyst needs.

Practise it#

Fill in the Department field in the exercise on this page. Then work through the XLOOKUP lesson, and try the HR roster interview drill, which asks you to look up a salary, count a department and list its staff without being told which functions to use.