Learn Excel
EN
Sets how numbers, dates and amounts look in the exercises, and whether you type , or ; between arguments, just like your own Excel.

Home › Excel functions › VLOOKUP › Practice

VLOOKUP practice exercises

Eight exercises that take you from your first lookup to two conditions and another sheet. Type real formulas, get a tip at every step and see the result straight away.

  1. Look up a typed value. Find the price of a Latte in F2: =VLOOKUP("Latte",A2:C7,3,FALSE)
  2. Use a cell as the lookup value. Find the calories of the item in E2: =VLOOKUP(E2,A2:C7,2,FALSE)
  3. Your turn: find a department. Show the department of employee 1004 in F2, using E2: =VLOOKUP(E2,A2:C7,3,FALSE)
  4. Approximate match: shipping cost. Find the shipping cost for the 7 kg parcel in E2: =VLOOKUP(D2,A2:B6,2,TRUE)
  5. Your turn: score to grade. Give Mia's grade in F2: =VLOOKUP(E2,A2:B6,2,TRUE)
  6. Hide #N/A with IFERROR. Look up the price of a Bagel in F2, and show "Not on menu" if it isn't there: =IFERROR(VLOOKUP(E2,A2:C7,3,FALSE),"Not on menu")
  7. Two conditions with a helper key. Find Ben's sales in the South region in H2: =VLOOKUP(F2&"-"&G2,A2:D7,4,FALSE)
  8. Look up from another sheet. Look up the price of the product in B2 in C2: =VLOOKUP(B2,Prices!A2:B7,2,FALSE)

What you'll practise

The eight exercises go from a first, simple lookup to the cases that trip people up at work. Each one adds one new idea:

  1. A typed lookup value: the basic VLOOKUP with the value in quotes.
  2. A cell as lookup value: the way you'll use it almost every time.
  3. Your turn: a full formula without tips, on a list of employees.
  4. Approximate match: finding the right price band for a weight.
  5. Your turn: turning a score into a grade with the same trick.
  6. IFERROR: your own text instead of #N/A when a value is missing.
  7. Two conditions: a helper key that joins two values with &.
  8. Another sheet: looking up in a price list on its own tab.

The first exercises guide you with a tip at every step. In the "your turn" exercises you write the formula yourself and only get help when you ask for it, so you find out what you really know.

Download the VLOOKUP practice file

Rather practise in your own Excel? The practice file has the same eight exercises, each on its own sheet. Type your formula in the yellow cell; the check cell next to the task tells you straight away whether it's right. An answers sheet at the end has the model formula for every exercise.

Download the practice file (.xlsx)

The file works in Excel for Windows and Mac and in Excel for the web. In a Dutch, German or French Excel the function names and separators appear in your own language automatically.

Checklist when your VLOOKUP doesn't work

  • Is the lookup value in the first column of your range? VLOOKUP only searches the leftmost column of the table you select.
  • Do you count the column number from the start of the range? In a range that starts at column C, column C is number 1.
  • Did you add FALSE as the last argument? Without it, Excel uses an approximate match and can return a wrong value without any warning.
  • Is the range locked with $ signs? When you copy the formula down, an unlocked range moves along with it.
  • Are the values really the same? A number stored as text, or a space at the end of a name, makes VLOOKUP return #N/A.

More VLOOKUP guides

The complete guideFind a value in a table and return a matching value from the same row.From another sheetPull data from a list on another tab or in another workbook.Multiple criteriaLook up a value that matches two conditions, like product and size.#N/A errorWhy VLOOKUP can't find your value, and how to fix or hide #N/A.Approximate matchFind the right band in a sorted table: discounts, rates and grades.

Frequently asked questions

How can I practise VLOOKUP?

Start with the exercises on this page: type the formulas in the spreadsheet above and you get a tip at every step. Then download the practice file and do the same exercises in your own Excel, without the tips.

Is VLOOKUP hard to learn?

The basic formula takes most people ten minutes. What takes practice are the details: the column number, exact versus approximate match and the #N/A error. That's what these exercises focus on.

Should I practise VLOOKUP or XLOOKUP?

Both. XLOOKUP is easier and more flexible, but VLOOKUP is still in millions of workbooks and job tests often ask for it. Once you know VLOOKUP, XLOOKUP takes only a few minutes to learn.