Skip to content

Exercise 26 of 41 · Lookup functions · Intermediate · 10 min

Find an Employee by ID with XLOOKUP

Excel version: XLOOKUP needs Excel 2021 or Microsoft 365; INDEX with MATCH works in every version.

Your task: In G3:G5, look up the name, department and office of the employee ID in G2.

Full instructions, scenario and goal

Scenario

The HR directory lists each employee's name, department, employee ID and office. The ID column comes after the name and the department, which is how the export arrived. A colleague types an employee ID into G2 and needs the name, department and office for that person, whichever ID they type.

Your task

  1. Look at the directory in A1:D9. Each employee ID appears once, in column C.
  2. The employee ID to look up is in G2.
  3. In G3, return the employee's name. In G4, return the department. In G5, return the office.
  4. Write a formula in each cell, then select Check Answer.

You are done when

  • G3, G4 and G5 contain formulas that use the ID in G2.
  • The name and the department come from columns to the left of the ID column.
  • The answers stay right when G2 changes or when the directory is re-sorted.

What you will practise

Retrieve values that sit to the left of the lookup column, using XLOOKUP, or INDEX with MATCH, when VLOOKUP cannot.

The idea in 30 seconds

XLOOKUP takes three main pieces: the value to find, the range to search, and the range to return from. Because the search range and the return range are separate, the result can come from any column, including columns to the left of the one you search. An optional fourth argument gives a result to show when nothing is found.

Read the full explanation below the sheet

Spreadsheet

G3
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9

Loading spreadsheet…

Keyboard shortcuts
Arrow keys
Move between cells
Shift + Arrow keys, or drag with the mouse
Select a range
Ctrl + Arrow keys
Jump to the edge of a block of data
Type, or F2, or double-click
Edit a cell (F2 switches arrow keys between moving and editing)
Click or arrow keys inside a formula
Insert a cell reference after =, +, (, comma and similar
F4
Cycle a reference through A1, $A$1, A$1, $A1
Enter / Tab while editing
Confirm and move down / right (Shift reverses)
Esc
Cancel the edit
Ctrl + C / X / V
Copy, cut and paste (formulas adjust like in Excel)
Delete
Clear the selected cells
Ctrl + Z / Ctrl + Y
Undo and redo
Ctrl + Enter
Check your answer
Tab when not editing
Leave the spreadsheet

Type your answer in cell G3. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.

New here? How an exercise works
  1. Read the task. Highlighted cells are yours to fill in; the rest are locked.
  2. Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
  3. Press Check answer. Stuck? Reveal hints one at a time.

The full explanation: the idea behind this exercise

XLOOKUP takes three main pieces: the value to find, the range to search, and the range to return from. Because the search range and the return range are separate, the result can come from any column, including columns to the left of the one you search. An optional fourth argument gives a result to show when nothing is found.

VLOOKUP cannot do that: it only returns columns to the right of its lookup column. Before XLOOKUP existed, the workaround was INDEX and MATCH. MATCH finds the position of the key in a column, and INDEX returns the value at that position of another column. Both approaches search by value, so the answer stays right when a list is re-sorted.

Skills you will practise

  • XLOOKUP
  • INDEX and MATCH
  • Lookup left
  • Vlookup limits

Topic guide: Excel Lookup Functions Practice: VLOOKUP, XLOOKUP, INDEX MATCH

Complete first: Look Up a Product's Price

Common mistakes to avoid

Using VLOOKUP when the answer is on the left
VLOOKUP always searches the first column of its table and can only return columns to the right of it, so it cannot reach the name or the department here.
Returning a row position instead of a value
MATCH on its own gives a position, such as 3. Give that position to INDEX to get the value.