What VLOOKUP Actually Does
VLOOKUP is the Excel function that searches for a value in the first column of a table, then returns a matching value from a column to its right. Its name is short for 'vertical lookup'. It is the tool you reach for when you need to pull data from one table into another — for example, matching a customer ID to a name, or a product code to its price.
This guide walks through the syntax, a copy-paste example you can try, the most common errors and how to fix them, and when a different function would be a better fit.
VLOOKUP works in Microsoft Excel, Google Sheets, and nearly every spreadsheet program on the market. The examples here are written for Excel, but the same formulas run in Google Sheets with only minor syntax changes, so the skill transfers wherever you end up working.
The VLOOKUP Syntax, Explained
VLOOKUP takes exactly four arguments. Once you understand the four pieces, the whole function starts to make sense.
The value you are searching for. This is usually a cell reference, like A2, pointing at the ID or code you want to match.
The range of cells that contains your data, such as D2:E20. The lookup column must be the first column of this range.
Which column of the range holds the answer you want to return. Count from the first column of the range, starting at 1.
Use FALSE for an exact match, which is what you want nearly always. TRUE gives approximate matches and only makes sense with sorted data.
Memorize the four arguments as 'what to find, where to look, which column to return, exact or not'. That mental model covers 90% of real VLOOKUP use.
A Copy-Paste Example
Here is a realistic setup. Suppose column A has employee IDs, and you want to fill in each person's name from a separate table in columns D and E.
Concretely, imagine cell A2 holds the value 1001, and the reference table in D2:E20 lists IDs down column D (1001, 1002, 1003...) and names down column E (Sarah, Tom, Priya...). The formula in B2 searches for 1001 in column D, finds it on the first row of the table, and returns 'Sarah' from the second column of the range.
Put the IDs you want to match in column A (A2 downward). Put the reference table in D2:E20, with IDs in column D and names in column E.
In cell B2, enter =VLOOKUP(A2,$D$2:$E$20,2,FALSE). This searches for A2's ID in column D and returns the matching name from column E.
Drag the formula down column B. Every row now shows the name that matches its ID. The dollar signs lock the table range so it does not shift as you copy down.
Always lock your table array with dollar signs ($D$2:$E$20). It is the single most common mistake that breaks VLOOKUP formulas when you fill them down.
Fixing the #N/A Error (and Others)
The #N/A error is the one people hit most. It means VLOOKUP could not find a match, and there are a few common causes.
Two other errors appear less often but still trip people up. #VALUE! usually means a text argument was not quoted properly, while #REF! means a column reference no longer points at valid cells — typically after you delete a column. Both are fixed by checking the formula's range and arguments rather than re-typing the whole thing.
A trailing space in the lookup value or in the table will break the match. Use =TRIM(A2) or check for hidden spaces in your data.
Text-formatted numbers do not match numeric values. If one column stores IDs as text and the other as numbers, VLOOKUP fails even though the values look the same.
VLOOKUP can only search the first column of its range. If your IDs are not in the first column, reorder the table or use INDEX and MATCH instead.
Wrap the formula in IFERROR to show a clean message instead of an ugly error: =IFERROR(VLOOKUP(A2,$D$2:$E$20,2,FALSE),"Not found").
If you are getting #N/A but the value clearly exists, the data type mismatch is the usual culprit. Convert both columns to the same type and retry.
Using VLOOKUP Across Sheets
VLOOKUP is at its most useful when the two tables live in different sheets or workbooks. Referencing another sheet is straightforward.
The key detail is that the lookup column must still be the first column of the range you reference on that other sheet. If the pricing table stores product names in column B and prices in column C, you must set the range starting at column B, not column A, so the names are in the first position.
To look up a table on a sheet named 'Pricing', write =VLOOKUP(A2,Pricing!$A$2:$B$50,2,FALSE). The sheet name comes before the exclamation mark.
For a closed workbook, include the file path and name, like =VLOOKUP(A2,'[Prices.xlsx]Sheet1'!$A$2:$B$50,2,FALSE). Single quotes are needed if the path has spaces.
Give your lookup range a named range, like ProductPricing. Formulas become readable and less likely to break when columns move.
Use a named range for tables you reference often. =VLOOKUP(A2,ProductPricing,2,FALSE) is far easier to read and maintain.
When to Use Something Else
VLOOKUP is powerful, but it has real limits. Knowing when to reach for another tool saves you hours of frustration.
The clearest rule: if you keep contorting your data to make VLOOKUP work — moving a column just to get it first, or building helper columns — you are probably using the wrong tool. A cleaner approach usually exists, and switching is a sign of skill, not weakness.
In modern Excel, XLOOKUP can search any column, not just the first, and returns a clean blank instead of #N/A. If your Excel version supports it, prefer it for new work.
When you need to return a value from a column to the left of the lookup column, use INDEX/MATCH. It is also faster on very large datasets.
If your goal is to add up values rather than return one, SUMIFS (or SUMIF) is the right tool, not VLOOKUP.
When you are combining two large tables, Power Query's merge feature is cleaner and more robust than formula lookups.
On a resume or in a job, knowing XLOOKUP and INDEX/MATCH alongside VLOOKUP signals genuinely modern spreadsheet skills, not just the basics.
Practice Scenarios to Try
The fastest way to make VLOOKUP stick is to use it on real problems. Here are three you can build in a few minutes.
A good habit is to start every practice task with a sketch on paper: write down what you are looking up, where the source data lives, which column holds the answer, and whether you need an exact match. Getting that four-part plan right before typing makes the formula write itself.
Create a sales log of product codes and a separate price table. Use VLOOKUP to fill the price next to each code, then multiply by quantity for revenue.
Build an orders table with customer IDs and a customer table with names and regions. Look up the name and region for each order. This mirrors the classic 'order log plus master customer list' pattern used across real businesses.
Put two versions of a product list side by side, then use VLOOKUP to check which items in list one also appear in list two. A missing result tells you the item is new or renamed.
Make one sheet of staff IDs, names, and departments, then a lookup sheet where typing an ID returns the person's details.
After each practice project, try rewriting it with XLOOKUP and with INDEX/MATCH. Comparing the three versions teaches you why each function exists and which fits best for different situations.
Your Next Step
VLOOKUP is one of the highest-value Excel functions you can learn because it appears in almost every real spreadsheet job. Master the four arguments, the dollar-sign lock, and the common errors, and you will handle the vast majority of lookups you ever meet.
Set yourself one realistic goal for the week: build a two-sheet lookup that a colleague could actually use, and make it error-proof with IFERROR and a named range. Completing that single project teaches you more about real-world spreadsheets than a month of watching tutorials.
Open a spreadsheet right now and build the product-price example. Ten minutes of hands-on practice will lock in the pattern far better than reading about it ever will, and it gives you a working file you can reuse.