Use VLOOKUP to find an employee’s department
BeginnerMany organizations keep a simple employee directory (ID to department). Instead of searching manually, you can use VLOOKUP to pull the right department automatically.
VLOOKUP syntax:=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
In this sheet:
- The Directory table is in A2:B7 (headers in row 2, data in rows 3 to 7)
- The Lookup table is in A10:B14
Your task
- Click cell B11.
- Type a VLOOKUP formula that uses the employee ID in A11 and searches the directory in A3:B7.
- Return the Department from the directory (the second column of the directory table).
- Use exact match so Excel only matches the exact employee ID.
- Lock the directory range with $ so you can copy the formula.
- Copy your formula down from B11 to B14.
When you’re done, cells B11:B14 should show the correct departments for each employee ID.
Need some help?
Hint 1
VLOOKUP needs four parts: the lookup value, the table to search, which column to return, and whether the match is exact or approximate.
Hint 2
In B11, your lookup value is A11. Your table is the directory in A3:B7, and you want to return column 2 of that table.
Hint 3
Use $ to lock the directory range (so it doesn’t move when you fill down), and use FALSE for an exact match.
Use VLOOKUP to find an employee’s department
BeginnerMany organizations keep a simple employee directory (ID to department). Instead of searching manually, you can use VLOOKUP to pull the right department automatically.
VLOOKUP syntax:=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
In this sheet:
- The Directory table is in A2:B7 (headers in row 2, data in rows 3 to 7)
- The Lookup table is in A10:B14
Your task
- Click cell B11.
- Type a VLOOKUP formula that uses the employee ID in A11 and searches the directory in A3:B7.
- Return the Department from the directory (the second column of the directory table).
- Use exact match so Excel only matches the exact employee ID.
- Lock the directory range with $ so you can copy the formula.
- Copy your formula down from B11 to B14.
When you’re done, cells B11:B14 should show the correct departments for each employee ID.
Need some help?
Hint 1
VLOOKUP needs four parts: the lookup value, the table to search, which column to return, and whether the match is exact or approximate.
Hint 2
In B11, your lookup value is A11. Your table is the directory in A3:B7, and you want to return column 2 of that table.
Hint 3
Use $ to lock the directory range (so it doesn’t move when you fill down), and use FALSE for an exact match.