Intro to Git — Snapshots, Branches & Sharing
WorkshopyOfficial10 stepsQuiz9 interactiveDiagram
gitversion controlbeginners
VLOOKUP and XLOOKUP practice exercises on live work sheets: exact match, column numbers, $ locking, fixing #N/A and looking left, checked as you go.

WorkshopyOfficial
78 lessons · used by 2 teachers
Sign in to copy it to your workspace. It’s free.
10 steps · 2 quizzes · open any step, or read it here
In this lesson you practise VLOOKUP and XLOOKUP on small work sheets: you look up prices, product names and emails from a second list, and the sheet checks each exercise as you go. Every lookup is animated cell by cell, so you see exactly where Excel looks and what it brings back.
Watch the sheet below. Four orders have a product code but no price; the price list sits underneath. One formula finds each price.
Interactive · One formula, every price — try it in the lesson
In this lesson you'll:
$, so a formula fills down without going wrongIt takes about 60 minutes: nine short steps, three exercises you do in the sheet, a practice step and two quizzes.
Takeaway: a lookup copies a value from another list by matching a code, so you never retype a price.
A lookup answers one question: "In this list, find the row that has my code, and give me the value in another column of that row."
VLOOKUP ("vertical lookup") takes four arguments:
=VLOOKUP(what to find, where to look, which column to return, FALSE)
B9.A2:D7. VLOOKUP searches only its first column.4 means the 4th column of A2:D7, which is D.VLOOKUP walks down the first column from the top and stops at the first match. Capital letters don't matter: p-104 finds P-104.
Try it: pick each code below and watch the search. Then pick P-106, which isn't in the list.
Interactive · Watch VLOOKUP find a row — try it in the lesson
What did D9 show for P-106? That is #N/A, "not available": Excel's way of saying "I looked and it isn't there".
Takeaway: VLOOKUP goes down the first column, stops at the match, then moves across to the column you asked for.
Now you write the formulas. The orders at the top need a product name and a price; the price list is in rows 7 to 12.
To write a formula, select a cell, type in the bar above the sheet and press Enter. Every formula starts with =.
The lookup for the product name of order 1001 reads: find B2 in the list A7:C12 and return its 2nd column, exactly.
Try it: do the three tasks under the sheet. Each one ticks when the right value appears. Open a hint if you're stuck.
Interactive · Write your first VLOOKUP — try it in the lesson
Did you notice how D3's formula is the same as D2's with one letter changed? That is what fill down will do for you in step 6.
Takeaway: =VLOOKUP(code, table, column, FALSE): point at the code's cell, never type the code into the formula.
The third argument is the one people get wrong most. It doesn't mean the sheet's column; it means the position inside the table you gave.
With the table A2:D7, column 1 is A, column 2 is B, column 4 is D. If the table started in column C (C2:F7), column 1 would be C.
Two errors guard the edges:
0 or a negative number gives #VALUE!: there is no column 0.There is also a quiet failure. The number is typed into the formula, so it doesn't change when the table changes. If someone inserts a column inside the table, Excel widens the range for you, but your 4 still means "the 4th column", which is now something else.
Try it: move the slider through every column number. Then turn on the switch and look at what column 4 returns.
Interactive · Count the column number — try it in the lesson
With the Supplier column inserted, column 4 returns the stock instead of the price, and nothing on the sheet looks wrong. That is one reason XLOOKUP, in step 8, doesn't use a column number at all.
Takeaway: count the column from the table's first column, and check your lookups after anyone inserts a column.
The last argument decides how VLOOKUP matches:
| Last argument | What VLOOKUP does |
|---|---|
FALSE (or 0) |
Exact match only. Not found: #N/A. |
TRUE (or 1) |
Approximate match: if the value isn't there, it takes the nearest smaller value. The first column must be sorted A to Z or smallest to largest. |
| left out | The same as TRUE. |
So a VLOOKUP written without its last argument is an approximate match. When the code is in a sorted list, you get the right row either way. When it isn't, an approximate match doesn't say so: it quietly returns the row of the nearest smaller code. And on a list that isn't sorted, it can return the wrong row even when the code is there.
Try it: look up P-106 with each last argument. Then look up P-104 and P-099.
Interactive · Exact or approximate match — try it in the lesson
With TRUE or nothing, P-106 got the price of P-105, a product the order never asked for. With FALSE you get #N/A, which is a problem you can see and fix.
Takeaway: for codes, names and IDs, always end VLOOKUP with FALSE. Use an approximate match only on purpose, as step 9 shows.
Four questions on the steps so far.
Question 1. Where does VLOOKUP search for the value you want to find?
Question 2. Which cell's value does =VLOOKUP(B2, C2:F9, 3, FALSE) return from the matching row?
Question 3. A VLOOKUP has no last argument, and the code isn't in the sorted list. What does it show?
Question 4. What does =VLOOKUP(B2, A2:C9, 4, FALSE) show?
You rarely write one lookup. You write it once and fill it down the column, so every order gets its price.
When a formula is copied down one row, Excel moves every reference in it down one row too. That is right for the code (B2 becomes B3) but wrong for the table: A10:C15 becomes A11:C16, then A12:C17, and the top of the price list falls out of it.
A $ stops a reference from moving. $A$10:$C$15 stays the same in every copy: $ before the column letter locks the column, $ before the row number locks the row.
To add the $ signs, select the range in the formula and press F4 in Excel for Windows, or Cmd+T in Excel for Mac. In Google Sheets it is F4 too.
Try it: do the two tasks: first fill down as the formula is, then lock it and fill again.
Interactive · Lock the table, then fill down — try it in the lesson
Which codes went wrong without the $? Those that sit above the first row of their copy's table: each copy's table starts one row lower than the one before. On a longer sheet the wrong rows are easy to miss.
Takeaway: lock the lookup table with $ before you fill a lookup down: $A$10:$C$15.
#N/A means "no match". When you're sure the value is in the list, one of these is usually the reason:
"P-102 " with a space at the end isn't "P-102". Data pasted from other systems often has them. Fix: TRIM(B3) removes spaces at the start and end, and doubles in the middle.1002 typed as text doesn't match the number 1002. In Excel such a cell is aligned left and often has a small green triangle. Fix: convert the column (Data > Text to Columns > Finish), or look up VALUE(B3).$ and the formula was filled down. Fix: step 6.IFERROR(formula, "text") shows the formula's result, unless it's an error; then it shows your text. Use it only after you've ruled out reasons 1 to 3: it hides every error, including the ones you'd want to see.
Try it: select B3 and B4 to find out why they fail, then do the two tasks.
Interactive · Fix the #N/A errors — try it in the lesson
Takeaway: before you hide a #N/A, find its reason: spaces, text numbers, a moved table, or a code that really is missing.
XLOOKUP is the newer lookup, in Excel for Microsoft 365, Excel 2021 and later, and Google Sheets. Instead of a table and a column number, it takes two columns:
=XLOOKUP(what to find, where to look, what to return, [if not found])
B2:B7 and A2:A7. The return column can be on the left: VLOOKUP can't do that.FALSE to forget.In an older Excel, the same left lookup is written with INDEX and MATCH: =INDEX(A2:A7, MATCH(B9, B2:B7, 0)).
Try it: the staff list has emails on the left and names on the right. Find the emails by name.
Interactive · Look left with XLOOKUP — try it in the lesson
If you tried VLOOKUP first, you saw why XLOOKUP exists: VLOOKUP searched the email column, the first column of its table, and found no names there.
Takeaway: =XLOOKUP(code, lookup column, return column, "Not found"): two columns, any order, exact by default.
Sometimes you want the nearest smaller value. Commission rates, shipping prices, tax brackets and grades all come in bands: "from 5,000 the rate is 4%". The list holds only where each band starts.
Both lookups can read a band table:
=XLOOKUP(B7, A2:A5, B2:B5, , -1): the 5th argument -1 means "exact match, or the next smaller value". The empty 4th argument (two commas) leaves "if not found" out.=VLOOKUP(B7, A2:B5, 2, TRUE): the approximate match from step 5, used on purpose. The first column must be sorted smallest to largest.A value exactly on a boundary belongs to the band that starts there: sales of exactly 10,000 get 6%.
Try it: drag the slider and watch which band each sales figure lands in. Stop exactly on 5,000, 10,000 and 20,000.
Interactive · Commission bands — try it in the lesson
The rates in this sheet are illustrative, not a real commission plan.
Takeaway: for bands, list where each band starts, sorted, and use XLOOKUP with -1 or VLOOKUP with TRUE.
Work out each answer before you check it. All the exercises use this price list in A1:C7:
| A | B | C | |
|---|---|---|---|
| 1 | Code | Product | Price |
| 2 | P-101 | Desk lamp | 24 |
| 3 | P-102 | Office chair | 149 |
| 4 | P-103 | Monitor stand | 39.50 |
| 5 | P-104 | USB-C hub | 18.75 |
| 6 | P-105 | Wireless mouse | 21 |
| 7 | P-107 | Notebook pack | 7.20 |
Six questions on the whole lesson.
Question 1. You filled a VLOOKUP down a column. Some rows show #N/A, though their codes are in the list. What is the most likely cause?
Question 2. Which reference stays the same when the formula is copied down and across?
Question 3. The code in B4 is "P-102 " with a space at the end, and the list has "P-102". Which formula finds it?
Question 4. Names are in column C and emails in column A. Which formula finds the email for the name in F2?
Question 5. Which of these are true of XLOOKUP? (pick all that apply)
Question 6. Bands start at 0 (2%), 5,000 (4%), 10,000 (6%) and 20,000 (8%). Which rate does a salesperson with 19,999 in sales get?
Workshopy shares this lesson under CC BY 4.0. Anyone may use and change it, with credit to you. Anyone can learn it here, free. A copy in your workspace is yours to change and run live; your students never need an account.
Version 1Current
· First published
Copies are made from the current version. A copy you already have doesn’t change.
WorkshopyOfficial10 stepsQuiz9 interactiveDiagram
gitversion controlbeginners
WorkshopyOfficial16 steps2 quizzes15 interactiveDiagram
workshopyproduct touronboarding
WorkshopyOfficial10 steps2 quizzes10 interactive
data-literacystatisticsmean and median
WorkshopyOfficial11 steps2 quizzes10 interactive
data-literacycorrelationcausation
WorkshopyOfficial9 steps2 quizzes7 interactive
pythonpandasdataframe
WorkshopyOfficial10 steps2 quizzes9 interactive
excelsumifcountif
Comments
No comments yet. Be the first to say how it went.