VLOOKUP finds a value in the first column of a table and returns something from the same row. It’s how Excel joins two lists.
=VLOOKUP(, click to change$D$2:$F$7, hover to view, click to changeFALSE)hover to view
ƒx formula
A
B
C
D
E
F
1
SKU
Result
SKU
Product
Price
2
SKU-1029
89
SKU-1041
Office Chair
189
3
SKU-1063
SKU-1007
Desk Lamp
34
4
SKU-1099
SKU-1112
Standing Desk
449
5
SKU-1029
Keyboard
89
6
SKU-1063
Wireless Mouse
29
7
SKU-1005
USB-C Dock
79
B2 = #N/A SKU-1099 isn’t in the price list, so there’s nothing to bring back.
B2 = Wireless Mouse SKU-1063’s product, from the price list’s second column.
B2 = 29 SKU-1063’s price, from the price list’s third column.
B2 = Keyboard SKU-1029’s product, from the price list’s second column.
B2 = 89 VLOOKUP found SKU-1029 in the price list’s first column and brought back the third: its price.
Say you have a price list, and an order that records only product codes. VLOOKUP takes each code,
finds it in the price list and returns that product’s price, so nobody copies prices across by
hand. The same formula fills in an employee’s department from their ID, a customer’s region from
their account number, or a grade from a score.
It’s a staple of Excel tests for job applicants, and it has a few traps that return a wrong number
without any warning. Below you build one step by step, then see the formula taken apart one
argument at a time, each with a small sheet where you can make the classic mistakes yourself.
Lesson
This is Witsheet, Witsy’s in-browser spreadsheet. It works like Excel and checks each step as you
go.
How VLOOKUP finds an answer
VLOOKUP takes up to four arguments: what to find, where to look, which column to bring back, and
whether only an exact match will do. It searches down the table’s first column, stops at the first
match, then steps across that row to the column you numbered.
ƒx formula
A
B
C
D
E
F
1
SKU
Result
SKU
Product
Price
2
SKU-1029
89
SKU-1041
Office Chair
189
3
SKU-1007
Desk Lamp
34
4
SKU-1112
Standing Desk
449
5
SKU-1029
Keyboard
89
6
SKU-1063
Wireless Mouse
29
7
SKU-1005
USB-C Dock
79
B2 = Keyboard Same row, second column: the product’s name.
B2 = SKU-1029 Column 1 is the column VLOOKUP searched, so it hands back the SKU you gave it.
B2 = 89 VLOOKUP went down the price list’s first column to SKU-1029 in row 5, then across to its third column, and brought back the price.
=VLOOKUP(A2, hover to view$D$2:$F$7, hover to view, click to changeFALSE)hover to view
A2lookup_value
What to find: the SKU in A2.
$D$2:$F$7table_array
Where to look: the price list. VLOOKUP searches its first column, top down.
321col_index_num
Which column of the table to bring back, counted from its left edge.
FALSErange_lookup
FALSE: exact matches only.
Point at the value, don’t type it
The first argument should be the cell that holds the value. A formula that reads A2 answers for
whatever A2 holds, so it can be copied down a whole order and each line looks up its own SKU.
ƒx formula
A
B
C
D
E
F
1
SKU
Price
SKU
Product
Price
2
SKU-1029
89
SKU-1041
Office Chair
189
3
SKU-1099
SKU-1007
Desk Lamp
34
4
SKU-1029
SKU-1112
Standing Desk
449
5
SKU-1029
Keyboard
89
6
SKU-1063
Wireless Mouse
29
7
SKU-1005
USB-C Dock
79
B2 = 89 The same 89, but the SKU is now part of the formula. Copy it down an order and every line gets the keyboard’s price.
B2 = #N/A The price list has no SKU-1099. From an exact-match lookup, #N/A is the right answer: the product is missing.
B2 = #N/A A4 looks like SKU-1029, but it ends in a space, so it isn’t the same text. A #N/A on a value you can see in the list is nearly always this, or a number stored as text.
B2 = 89 B2 reads its SKU from A2. Change A2 and the price follows, and the formula can be copied down an order with each line reading its own SKU.
=VLOOKUP(, $D$2:$F$7, 3, FALSE)click to change
A2"SKU-1029"A3A4lookup_value
What to find. Point it at a cell, or type the value in.
A #N/A on a value you can see in the list is usually the data, not the formula: a trailing space,
or a number stored as text. Data cleaning practice covers the fixes.
Lock the table before you fill down
A lookup is rarely written once. Fill it down a column and the table range moves with it, one row
per line, until the top of the list drops out of reach. Lines further down still find their
products, so the mistake shows up on some rows only.
ƒx formula
A
B
C
D
E
F
1
SKU
Price
SKU
Product
Price
2
SKU-1029
89
SKU-1041
Office Chair
189
3
SKU-1112
449
SKU-1007
Desk Lamp
34
4
SKU-1063
29
SKU-1112
Standing Desk
449
5
SKU-1005
79
SKU-1029
Keyboard
89
6
SKU-1007
34
SKU-1063
Wireless Mouse
29
7
SKU-1005
USB-C Dock
79
B6 = #N/A This is D2:F7 after filling down from B2: it slid one row per line and now starts at row 6, below SKU-1007 in row 3. This line shows #N/A while the four above look fine.
B6 = 34 With dollar signs on both corners, every copy of the formula searches D2:F7, and SKU-1007 is found in row 3.
=VLOOKUP(A6, , 3, FALSE)click to change
$D$2:$F$7D6:F11table_array
Where to look. Filled down from B2, a range without dollar signs moves a row with every line.
Write the range as $D$2:$F$7 and it stays put wherever the formula goes. You don’t type
the dollar signs: put the cursor on the range in the formula and press F4 (Fn + F4 on most Mac
keyboards).
Count columns from the table’s edge
The column number counts from the table’s left edge, not from column A. And because it’s typed in,
inserting a column into the table later moves the data but not the number, so the formula quietly
starts returning the column next door.
ƒx formula
A
B
C
D
E
F
1
SKU
Result
SKU
Product
Price
2
SKU-1029
89
SKU-1041
Office Chair
189
3
SKU-1007
Desk Lamp
34
4
SKU-1112
Standing Desk
449
5
SKU-1029
Keyboard
89
6
SKU-1063
Wireless Mouse
29
7
SKU-1005
USB-C Dock
79
B2 = Keyboard The table’s second column is E on the sheet: the product name.
B2 = #REF! The table is three columns wide. There’s no fourth column to return, so VLOOKUP gives #REF!.
B2 = 89 Price sits in column F of the sheet, but it’s the third column of the table, so the number is 3.
=VLOOKUP(A2, $D$2:$F$7, , FALSE)click to change
324col_index_num
Which column of the table to bring back. This table starts at column D, so D is column 1.
End with FALSE
Leave off the last argument and VLOOKUP uses approximate match. It assumes the first column is
sorted and, for a value that isn’t there, returns the nearest one below. That’s right for banding,
such as a tax rate from an income or a grade from a score. For codes, names and IDs it’s a wrong
answer that looks right.
ƒx formula
A
B
C
D
E
F
1
SKU
Price
SKU
Product
Price
2
SKU-1016
65
SKU-1002
Notebook Stand
39
3
SKU-1010
SKU-1005
USB-C Dock
79
4
SKU-1007
Desk Lamp
34
5
SKU-1016
Headset
65
6
SKU-1029
Keyboard
89
7
SKU-1041
Office Chair
189
B2 = #N/A SKU-1010 isn’t in the list, and with FALSE, VLOOKUP says so.
B2 = 34 SKU-1010 isn’t in the list. Approximate match settles for the nearest SKU below it, SKU-1007, and returns the desk lamp’s price. Nothing on the sheet says it’s wrong.
B2 = 65 SKU-1016 is in the list, so the exact match finds it.
B2 = 65 SKU-1016 is in the list, so approximate match lands on it too. The two agree until a SKU is missing, which is why the mistake goes unnoticed. Try A3.
=VLOOKUP(, $D$2:$F$7, 3click to change, ), click to change
A2A3lookup_value
A SKU in the list, and one that isn’t.
FALSETRUE(left off)range_lookup
FALSE for exact matches. TRUE, or leaving it off, lets VLOOKUP settle for the nearest value below.
Catch #N/A, not every error
From an exact-match lookup, #N/A means the value isn’t in the list. It spreads, though: a line total
that multiplies it is #N/A, and so is any total that adds that line. Wrapping the lookup shows your
own text instead. While you’re still building a sheet, the raw #N/A is the more useful signal.
ƒx formula
A
B
C
D
E
F
1
SKU
Product
SKU
Product
Price
2
SKU-1029
Keyboard
SKU-1041
Office Chair
189
3
SKU-1099
SKU-1007
Desk Lamp
34
4
SKU-1112
Standing Desk
449
5
SKU-1029
Keyboard
89
6
SKU-1063
Wireless Mouse
29
7
SKU-1005
USB-C Dock
79
B2 = #REF! The column number is wrong, and IFNA lets you see it: #REF! isn’t #N/A, so it passes straight through.
B2 = Not listed IFERROR catches the #REF! too, so a broken formula reads like a missing product. That’s why IFNA is the safer catch for lookups.
B2 = Not listed SKU-1099 isn’t in the list. The lookup returns #N/A, and the wrapper shows your text instead.
B2 = Keyboard SKU-1029 is in the list, so the wrapper passes the product name through untouched.
=(VLOOKUP(click to change, $D$2:$F$7, click to change, FALSE), "Not listed")click to change
IFNAIFERRORwrapper
IFNA swaps in your text for #N/A only. IFERROR does it for every error.
A2A3lookup_value
A SKU in the list, and one that isn’t.
24col_index_num
2 is right. 4 is a mistake: the table has three columns.
If your Excel has XLOOKUP, use that
XLOOKUP does the same job without most of these traps. It’s in Microsoft 365, Excel 2021 and later,
Excel for the web and Google Sheets. It matches exactly unless you ask otherwise, takes the column to
return as a range rather than a number, can return a column to the left of the one it searches, and
has a fallback for missing values built in. You still lock its ranges before you fill it down.
ƒx formula
A
B
C
D
E
F
1
SKU
Result
Product
SKU
Price
2
SKU-1029
89
Office Chair
SKU-1041
189
3
SKU-1099
Desk Lamp
SKU-1007
34
4
Standing Desk
SKU-1112
449
5
Keyboard
SKU-1029
89
6
Wireless Mouse
SKU-1063
29
7
USB-C Dock
SKU-1005
79
B2 = #N/A Nothing matches and there’s no fallback, so XLOOKUP shows #N/A, as VLOOKUP would.
B2 = 0 Nothing matches, so XLOOKUP shows the fallback. A 0 keeps the order total adding up.
B2 = Not listed Nothing matches, so XLOOKUP shows the fallback, with no IFERROR around it.
B2 = Keyboard Product sits to the left of the SKUs, where VLOOKUP can’t reach. XLOOKUP returns it like any other column.
B2 = 89 An exact match with no FALSE to remember, and the price comes from a column you point at rather than a number you count.
=XLOOKUP(, click to change$E$2:$E$7, hover to viewclick to change, ), click to change
A2A3lookup_value
What to find.
$E$2:$E$7lookup_array
Where to look: just the SKU column.
$F$2:$F$7$D$2:$D$7return_array
What to bring back: a column you point at, on either side of the SKUs.
"Not listed"0(left off)if_not_found
What to show when nothing matches. Leave it off and you get #N/A.
So if your Excel has XLOOKUP, there’s no reason to write a new VLOOKUP. You’ll still read plenty of
them: most workbooks you inherit are built on VLOOKUP, Excel 2019 and older don’t have XLOOKUP (a
formula using it shows #NAME? there), and many Excel tests for job applicants still ask for VLOOKUP by
name. XLOOKUP practice runs a similar job the newer way.
Download the VLOOKUP practice sheet
To carry on in your own Excel, download the workbook. It’s the same kind of job at full size: 60 products on one sheet, 120 orders that record only the SKU on another, and five tasks to work through, with the answers on a sheet of their own.
Product price list + orders
A 60-product catalog and a separate order log that only records SKUs — built for lookup practice. Pull prices into the orders with VLOOKUP or XLOOKUP, then compute order totals.
180 rows · SKU · Product · Category · Unit Price · In Stock
Free · fictional data · opens in Excel and Google Sheets
Witsy’s Excel course teaches a few hundred small skills, called Wits,
the way this page teaches VLOOKUP: in a real spreadsheet, a step at a time. The placement test
finds the ones you already have.
Your Wit map
Test your spreadsheet Wits.
Take a few minute test to see how good you actually are with spreadsheets. Witsy
creates a map of the Wits you should learn next.