VLOOKUP: practice exercises
Pulling information from another table. 4 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
In D2, look up each employee's manager using the table in H1:J5.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Staff ID | Employee | Team | Manager | Office | Team | Manager | Office | ||
| 2 | 1001 | Grace | Sales | 2.03 | Sales | B. Reid | 2.03 | |||
| 3 | 1002 | Tom | Finance | 1.12 | Finance | D. Fraser | 1.12 | |||
| 4 | 1003 | Priya | Support | G.02 | Support | A. Morgan | G.02 | |||
| 5 | 1004 | Marek | Marketing | 3.08 | Marketing | C. Lowe | 3.08 | |||
| 6 | 1005 | Nia | Sales | 2.03 | ||||||
| 7 | 1006 | Ollie | Finance | 1.12 | ||||||
| 8 | 1007 | Lucy | Support | G.02 | Staff ID | 1007 | ||||
| 9 | 1008 | Hamza | Marketing | 3.08 | Name | Lucy | ||||
| 10 | 1009 | Rachel | Sales | 2.03 | ||||||
| 11 | 1010 | Sunil | Finance | 1.12 | ||||||
| 12 | 1011 | Tanya | Support | G.02 | ||||||
| 13 | 1012 | Will | Marketing | 3.08 |
How it works
VLOOKUP(what to find, table, column number, FALSE) finds a value in the first column of a table and returns the value from another column on the same row.
Always finish with FALSE for an exact match. Leave it out and Excel guesses the nearest match, which is a common cause of wrong data. Lock the table with $ if you copy the formula down.
=VLOOKUP(A2,$D$2:$F$4,2,FALSE)
What VLOOKUP does
VLOOKUP finds something in the first column of a table, then brings back a value from the same row. It's like finding a name in a phone list, then reading across to the number.
It takes four things: VLOOKUP(what to find, the table, which column, FALSE).
Worked example
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Code | Item | Code | Item | Price | |
| 2 | P2 | P1 | Pens | £1.20 | ||
| 3 | P3 | P2 | Pads | £2.50 | ||
| 4 | P1 | P3 | Tape | £0.90 | ||
| 5 | P2 |
=VLOOKUP(A2in . First, what to find: the code in A2, which is P2.=VLOOKUP(A2,$D$2:$F$4in . Then the table, without its headings. VLOOKUP searches its first column, D, for P2. The table is locked with $ so it stays put when the formula is copied down.=VLOOKUP(A2,$D$2:$F$4,2in . Then which column to bring back, counting from the left of the table: Code is 1, Item is 2, Price is 3.=VLOOKUP(A2,$D$2:$F$4,2,FALSE)in . Last, FALSE, meaning find P2 exactly. P2 is on the second row of the table, and column 2 of that row says Pads.=VLOOKUP(A2,$D$2:$F$4,2,FALSE)in . Copied down, A2 becomes A4 and the locked table stays put, so every row finds its own item.
Watch out
- Always finish with FALSE. Without it, VLOOKUP does an approximate match: it assumes the first column is sorted and can quietly bring back the wrong row.
- The column number counts across the table, not the sheet. In
$D$2:$F$4, column F is 3, not 6. - VLOOKUP only looks right. What you're finding must be in the table's first column. (XLOOKUP, in a later lesson, doesn't have this limit.)
- #N/A means it couldn't find what you asked for. Check the spelling, and look for extra spaces.
What you'll practise
- In D2, look up each employee's manager using the table in H1:J5.
- In E2, look up each employee's office number.
- Make a quick search box. In I9, show the name of the employee whose ID is typed in I8.
- The VLOOKUP in D2 should give each person's manager from the table in H1:J6, but some of the managers are wrong. Fix it.