VLOOKUP is the function that turns a spreadsheet user into a spreadsheet person. It’s also the one that generates more confused Googling than everything else in Excel combined.
The idea is simple enough. You have an ID in one place and you need the matching information from somewhere else. A product code and you want the price. An employee number and you want the department. VLOOKUP goes and fetches it.
The trouble is that VLOOKUP has four arguments, one of which is optional and will quietly ruin your data if you leave it out. This guide covers the syntax, a worked example, the errors everyone hits, and an honest answer about whether you should be using XLOOKUP instead.
Table of contents
- What VLOOKUP actually does
- The syntax, argument by argument
- A worked example
- The FALSE argument you must not skip
- Why VLOOKUP breaks
- VLOOKUP vs XLOOKUP
- Where INDEX MATCH still fits
- Where lookup skills lead
- Turn Excel skills into a data career
- FAQs about VLOOKUP
What VLOOKUP actually does
VLOOKUP searches for a value in the first column of a range, then returns something from a column further right in that same row. The V stands for vertical, meaning it scans down a column rather than across a row.
Picture two sheets. One has a list of order IDs and quantities. The other has order IDs and customer names. VLOOKUP is how you get the customer name onto the first sheet without copying anything by hand.
That’s the whole concept. Everything else is syntax and edge cases.
The syntax, argument by argument
The function takes four arguments, in this order:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
The column index is a number, not a letter. If your range starts at column B and you want data from column D, the index is 3, because D is the third column of that range. Counting from the wrong place is one of the two most common mistakes.
A worked example
Say you have a product list on Sheet2. Column A holds product codes, column B holds names, column C holds prices. On Sheet1 you’ve got a product code in cell A2 and you want its price.
=VLOOKUP(A2, Sheet2!A:C, 3, FALSE)
Reading that in plain English: take the value in A2, look for it in the first column of Sheet2 columns A through C, and when you find it, return whatever is in the third column of that range. Only accept an exact match.
Drag it down and every row fills in. If you’re going to copy the formula around, lock the range with dollar signs so it doesn’t drift:
=VLOOKUP(A2, Sheet2!$A$1:$C$500, 3, FALSE)
Without those dollar signs, copying the formula down shifts the lookup range down too, and rows near the bottom start missing matches for no visible reason.
The FALSE argument you must not skip
This is the part worth reading twice.
The fourth argument controls whether Excel needs an exact match. FALSE means exact. TRUE, or leaving the argument out entirely, means approximate, and approximate matching assumes your first column is sorted in ascending order. If it isn’t, Excel returns whatever it happens to land on and reports no error at all.
That’s the worst failure mode in Excel. Not a broken formula, not an error message, just a plausible number that happens to be wrong. Reports get sent, decisions get made, and nobody notices for months.
Approximate matching has a legitimate use, mostly for tiered lookups like tax brackets or commission bands where you want the nearest value below. Outside that, type FALSE every single time.
Microsoft’s VLOOKUP documentation covers the full argument spec if you want the formal version.
Why VLOOKUP breaks
Four causes account for nearly every VLOOKUP problem.
- #N/A means no match was found. Usually it’s real, and often it’s a formatting mismatch. The number 1001 and the text “1001” look identical on screen and don’t match. Trailing spaces do the same thing. Run TRIM and check that both columns share a format.
- #REF! means your column index is too high. You asked for column 5 of a four-column range. Recount, remembering the index counts within your range, not the sheet.
- It can’t look left. VLOOKUP only returns data to the right of the match column. If the value you need sits in a column to the left, VLOOKUP cannot do it, no matter how you write it.
- Inserting a column breaks everything. Your index is a hard-coded number. Insert a column inside the range and the formula still points at position 3, which is now different data. This is why VLOOKUP formulas that worked for a year suddenly go wrong after someone tidies a sheet.
That last one is the strongest argument for moving on to a newer function.
VLOOKUP vs XLOOKUP
If you have Microsoft 365 or Excel 2021 and later, XLOOKUP does the same job and fixes the design problems.
=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, “Not found”)
You name the lookup column and the return column separately, so there’s no index number to miscount and nothing breaks when a column gets inserted. It defaults to exact match, removing the FALSE trap entirely. It searches in both directions, so looking left is fine. And the fourth argument sets what appears instead of #N/A.
So should you skip VLOOKUP entirely? No, and here’s the practical reason. Plenty of workplaces still run older Excel versions, and XLOOKUP simply doesn’t exist there. More to the point, you’ll inherit spreadsheets full of VLOOKUP formulas written by people who left years ago, and you need to read them.
Learn VLOOKUP so you can maintain what exists. Write XLOOKUP when you have the choice. Microsoft’s XLOOKUP documentation covers the newer syntax.
Where INDEX MATCH still fits
Before XLOOKUP arrived, the workaround for VLOOKUP’s limitations was combining two functions:
=INDEX(Sheet2!C:C, MATCH(A2, Sheet2!A:A, 0))
MATCH finds the row number where your value lives, and INDEX returns whatever sits in that row of a column you name. It looks in both directions and survives column insertion, same as XLOOKUP.
It’s harder to read, and it works in every version of Excel ever shipped. If you’re supporting an organization on older software, INDEX MATCH is the reliable choice. If everyone’s on a current version, XLOOKUP does the same thing more clearly.
Where lookup skills lead
Here’s something worth knowing early. A VLOOKUP is a join.
When you match an order to a customer record, you’re doing exactly what a database does with a LEFT JOIN, just one row at a time in a spreadsheet. Understanding that connection makes the jump to SQL much shorter, because you already grasp the concept and only need the syntax. Our guide to SQL joins covers the same idea at database scale.
That’s also where the ceiling shows up. VLOOKUP across 200,000 rows will make your laptop fan spin, and a database handles it instantly. Analysts move to SQL because spreadsheets stop coping, and our SQL study plan is a reasonable next step once lookups feel routine.
Before that, pivot tables are the natural companion skill. Our pivot table walkthrough pairs well with lookups, since one combines datasets and the other summarizes them.
Turn Excel skills into a data career
VLOOKUP shows up on data analyst job descriptions constantly, and it’s often tested in interviews with a live exercise. It’s a real signal that you can combine datasets rather than just read one.
On its own it isn’t enough. Employers hiring analysts want SQL, a visualization tool, and enough statistics to know when a number is misleading. Coding Temple’s data analytics bootcamp covers that full stack with project work built on realistic messy data, plus career services that start before you graduate.
Want to try it first? The free data analytics course costs nothing. When you’re ready to commit, apply to Coding Temple.
FAQs about VLOOKUP
What does VLOOKUP do in Excel?
It searches for a value in the first column of a range and returns a value from a column further right in the same row. It’s how you pull matching information from one table into another, like fetching a price using a product code.
Why is my VLOOKUP returning #N/A?
Excel couldn’t find the lookup value. The most common causes are a number stored as text in one column and as a number in the other, trailing spaces, or the value genuinely not existing. Run TRIM on both columns and confirm the formats match.
Can VLOOKUP look to the left?
No. VLOOKUP only returns data from columns to the right of the match column. Use XLOOKUP or INDEX MATCH when the value you need sits to the left.
Should I use TRUE or FALSE in VLOOKUP?
FALSE in almost every case. It forces an exact match. TRUE allows an approximate match and requires your first column to be sorted, and when it isn’t, Excel returns wrong data without any error. Only use TRUE for tiered lookups like tax brackets.
Is XLOOKUP better than VLOOKUP?
Yes, when it’s available. XLOOKUP defaults to exact match, searches in both directions, doesn’t break when columns are inserted, and handles missing values cleanly. It requires Microsoft 365 or Excel 2021 and later, so VLOOKUP is still worth knowing for older versions and inherited files.
What’s the difference between VLOOKUP and HLOOKUP?
VLOOKUP searches down the first column of a range, which suits data organized with records in rows. HLOOKUP searches across the first row instead, for data laid out horizontally. Vertical layouts are far more common, which is why VLOOKUP gets used more.