Course outline / Job and interview practice
Lesson 73 of 75 · Intermediate · 4 exercises
Interview practice: lookups
The lookup questions employers ask most, on an employee list.
What you'll practise
- Salary for an ID
- The same with VLOOKUP
- ID from a name, looking left
- Handle an ID that does not exist
Salary for an ID
Excel tests for jobs nearly always include a lookup. Remember the pattern: what to find, where to look, what to bring back.
In cell H2, find the salary (D2:D6) of employee 1004 (A2:A6) with XLOOKUP.
The same with VLOOKUP
Many employers still use VLOOKUP, so know both.
In cell H2, find the department of employee 1002 with VLOOKUP on A2:E6, returning column 3, exact match.
ID from a name, looking left
In cell H2, find the ID (A2:A6) of "Cal Reyes" (B2:B6). VLOOKUP can't look left, so use XLOOKUP or INDEX and MATCH.
Handle an ID that does not exist
In cell H2, look up the name (B2:B6) of employee 1009 (A2:A6) with XLOOKUP, showing "Unknown" if there is no match.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.