Confident Cells

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.

Your turn

In D2, look up each employee's manager using the table in H1:J5.

ABCDEFGHIJ
1Staff IDEmployeeTeamManagerOfficeTeamManagerOffice
21001GraceSales2.03SalesB. Reid2.03
31002TomFinance1.12FinanceD. Fraser1.12
41003PriyaSupportG.02SupportA. MorganG.02
51004MarekMarketing3.08MarketingC. Lowe3.08
61005NiaSales2.03
71006OllieFinance1.12
81007LucySupportG.02Staff ID1007
91008HamzaMarketing3.08NameLucy
101009RachelSales2.03
111010SunilFinance1.12
121011TanyaSupportG.02
131012WillMarketing3.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

ABCDEF
1CodeItemCodeItemPrice
2P2P1Pens£1.20
3P3P2Pads£2.50
4P1P3Tape£0.90
5P2
  1. =VLOOKUP(A2 in . First, what to find: the code in A2, which is P2.
  2. =VLOOKUP(A2,$D$2:$F$4 in . 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.
  3. =VLOOKUP(A2,$D$2:$F$4,2 in . Then which column to bring back, counting from the left of the table: Code is 1, Item is 2, Price is 3.
  4. =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.
  5. =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

  1. In D2, look up each employee's manager using the table in H1:J5.
  2. In E2, look up each employee's office number.
  3. Make a quick search box. In I9, show the name of the employee whose ID is typed in I8.
  4. 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.