Excel practice · Lookups

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

  1. Look up a course's credit hours
  2. Look up a product's price, from a wider table
  3. Look up a class's building, from a table that doesn't start at column A
  4. Look up an employee's department, by a numeric ID, mid-table
  5. Look up a shipping rate

The exercises

1. Look up a course's credit hours Medium

Your registrar's course table lists codes, names, and credit hours. Look up FIN220's credit hours.

ABCDE
1CodeCourseCreditsCode:FIN220
2ECN101Microeconomics3Credits:?
3FIN220Corporate Finance4
4STA150Statistics3
5MKT210Marketing3

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.

2. Look up a product's price, from a wider table Medium

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.

ABCDEFGH
1SKUItemPriceIn StockReorder LevelSKU:SKU-22
2SKU-14Notebook3.54010Price:?
3SKU-22Pen1.212030
4SKU-31Highlighter27520
5SKU-45Folder1.756015

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.

3. Look up a class's building, from a table that doesn't start at column A Medium

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.

ABCDEFG
1NotesCodeCourseBuildingCode:BIO210
2BIO210Cell BiologySci HallBuilding:?
3ENG101CompositionFenwick
4HIS150World HistoryOld Main
5PHY220MechanicsSci 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.

4. Look up an employee's department, by a numeric ID, mid-table Medium

HR's roster lists employee ID, name, department, and manager. Look up ID 1042's department, not the manager column right after it.

ABCDEFG
1IDNameDepartmentManagerID:1042
21021PriyaMarketingChenDepartment:?
31042OwenFinanceRivera
41058LucasOperationsChen
51077MayaEngineeringOkafor

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.

5. Look up a shipping rate Medium

Shipping cost depends on zone. Look up the rate for Zone C.

ABCDE
1ZoneRateZone:Zone C
2Zone A4.5Rate:?
3Zone B7
4Zone C9.5
5Zone D13

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 →

More lookups practice

All Excel practice exercises · Excel test prep