VLOOKUP practice exercises
5 graded problems with realistic data, all medium.
Searches the first column of a table for a match, and returns a value from another column in that same row. The 5 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.
New to the function? Read the VLOOKUP guide first: syntax, a worked example and the mistakes people make.
In this set
- Look up a course's credit hours
- Look up a product's price, from a wider table
- Look up a class's building, from a table that doesn't start at column A
- Look up an employee's department, by a numeric ID, mid-table
- Look up a shipping rate
The exercises
Your registrar's course table lists codes, names, and credit hours. Look up FIN220's credit hours.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Code | Course | Credits | Code: | FIN220 |
| 2 | ECN101 | Microeconomics | 3 | Credits: | ? |
| 3 | FIN220 | Corporate Finance | 4 | ||
| 4 | STA150 | Statistics | 3 | ||
| 5 | MKT210 | Marketing | 3 |
Enter a formula in E2.
Show a hint
VLOOKUP(lookup_value, table_range, column_index, FALSE). Table is A2:C5, Credits is the 3rd column: =VLOOKUP(E1,A2:C5,3,FALSE).
VLOOKUP is the formula recruiters test for. It pulls matching data from a bigger table without scrolling to find it by hand.
The campus store's price list tracks SKU, name, price, stock on hand, and reorder level: five columns. Look up SKU-22's price, not the whole row.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | SKU | Item | Price | In Stock | Reorder Level | SKU: | SKU-22 | |
| 2 | SKU-14 | Notebook | 3.5 | 40 | 10 | Price: | ? | |
| 3 | SKU-22 | Pen | 1.2 | 120 | 30 | |||
| 4 | SKU-31 | Highlighter | 2 | 75 | 20 | |||
| 5 | SKU-45 | Folder | 1.75 | 60 | 15 |
Enter a formula in H2.
Show a hint
table_array still has to span every column up to the one you need, even though Price isn't the last column here: =VLOOKUP(H1,A2:E5,3,FALSE).
Most real tables have more columns than you actually need. Picking the right col_index instead of always reaching for "the last column" is the real skill.
The registrar keeps a free-text Notes column in front of the actual course table. Look up where BIO210 meets, using only the real table (Code, Course, Building), not the notes column.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Notes | Code | Course | Building | Code: | BIO210 | |
| 2 | BIO210 | Cell Biology | Sci Hall | Building: | ? | ||
| 3 | ENG101 | Composition | Fenwick | ||||
| 4 | HIS150 | World History | Old Main | ||||
| 5 | PHY220 | Mechanics | Sci Hall |
Enter a formula in G2.
Show a hint
table_array doesn't have to start at column A. It has to start at the lookup column. That's B here, so the range is B2:D5, and Building is its 3rd column: =VLOOKUP(G1,B2:D5,3,FALSE).
Real sheets often have extra columns sitting in front of the table you actually need. VLOOKUP only cares where table_array itself starts, never where the sheet starts.
HR's roster lists employee ID, name, department, and manager. Look up ID 1042's department, not the manager column right after it.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | ID | Name | Department | Manager | ID: | 1042 | |
| 2 | 1021 | Priya | Marketing | Chen | Department: | ? | |
| 3 | 1042 | Owen | Finance | Rivera | |||
| 4 | 1058 | Lucas | Operations | Chen | |||
| 5 | 1077 | Maya | Engineering | Okafor |
Enter a formula in G2.
Show a hint
The lookup value here is a number, not text. VLOOKUP handles both the same way. Department is the 3rd column of a 4-column table, not the last: =VLOOKUP(G1,A2:D5,3,FALSE).
HR and ops systems look people up by ID constantly, and the column you actually need is rarely conveniently the last one in the table.
Shipping cost depends on zone. Look up the rate for Zone C.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Zone | Rate | Zone: | Zone C | |
| 2 | Zone A | 4.5 | Rate: | ? | |
| 3 | Zone B | 7 | |||
| 4 | Zone C | 9.5 | |||
| 5 | Zone D | 13 |
Enter a formula in E2.
Show a hint
=VLOOKUP(E1,A2:B5,2,FALSE).
Rate cards (shipping, tax, membership tiers) are a table VLOOKUP is built to search.
Solve these in a real grid
Type your formula, get graded instantly. Your first 10 exercises are free, no signup.
Start the VLOOKUP exercises →