VLOOKUP is one of the most useful functions in Excel. It lets you search for a value in one column and return a related value from another column in the same row. If you work with data regularly, learning VLOOKUP will save you hours of manual work.
This guide walks you through everything: syntax, real examples, common errors, and smarter alternatives.
What Is VLOOKUP and What Does It Do?
VLOOKUP stands for Vertical Lookup. It searches down a column (vertically) to find a match, then returns a value from a specified column in the same row.
Think of it like a phone book. You look up a name and it gives you the phone number next to it. VLOOKUP does exactly that with your spreadsheet data.
You use it when:
- You have two tables and want to pull data from one into the other
- You want to find a price, name, or score based on an ID
- You need to match records across different sheets or workbooks
The VLOOKUP Syntax Explained Simply
Here is the basic formula:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Let’s break each part down:
| Argument | What It Means |
|---|---|
| lookup_value | The value you are searching for |
| table_array | The range of cells that contains your data |
| col_index_num | The column number to return the result from |
| range_lookup | TRUE for approximate match, FALSE for exact match |
Always use FALSE as your fourth argument unless you specifically need an approximate match. Using TRUE (or leaving it blank) can return wrong results if your data is not sorted.
How to Use VLOOKUP in Excel: Step by Step
Step 1: Set Up Your Data
You need two things: a lookup value and a table to look it up in. The value you are searching for must be in the first column of your table. This is a hard rule in VLOOKUP.
For example, imagine you have this product table:
| Product ID | Product Name | Price |
|---|---|---|
| 101 | Keyboard | $25 |
| 102 | Mouse | $15 |
| 103 | Monitor | $180 |
And in another sheet, you have orders with Product IDs. You want to pull in the product name automatically.
Step 2: Write the Formula
Click the cell where you want the result. Then type:
=VLOOKUP(A2, Sheet1!$A$2:$C$10, 2, FALSE)
Here is what each part does in this example:
- A2 is the Product ID you are looking up
- Sheet1!$A$2:$C$10 is the table with your product data
- 2 means return the value from the second column (Product Name)
- FALSE means find an exact match only
Step 3: Lock Your Table Range
Notice the dollar signs in $A$2:$C$10. These lock the reference so it does not shift when you copy the formula down. Always use absolute references for your table array. Press F4 after selecting the range to add dollar signs automatically.
Step 4: Copy the Formula Down
Once your formula works in the first row, drag it down to apply it to all rows. Excel will keep the table range fixed and update the lookup value for each row.
Real World VLOOKUP Examples
Example 1: Pull Employee Salary by ID
You have a list of employee IDs in column A. Your HR table is on a separate sheet called “Employees” with IDs in column A and salaries in column D.
=VLOOKUP(A2, Employees!$A$2:$D$100, 4, FALSE)
This looks up the ID from A2 in the Employees sheet and returns the salary from column 4.
Example 2: Get Student Grades
Your grade table has student names in column A and final grades in column C.
=VLOOKUP("John Smith", $A$2:$C$50, 3, FALSE)
You can type the name directly in quotes, or reference a cell that contains the name.
Example 3: Look Up Data from Another Workbook
=VLOOKUP(A2, '[Sales Data.xlsx]Sheet1'!$A$2:$E$500, 3, FALSE)
The other workbook name goes in square brackets. Keep the source file open when you first create this formula.
VLOOKUP with Multiple Sheets
You can use VLOOKUP across sheets in the same workbook. Just include the sheet name before the range:
=VLOOKUP(A2, Sheet2!$A$2:$D$100, 2, FALSE)
If the sheet name has spaces, wrap it in single quotes:
=VLOOKUP(A2, 'Sales Data'!$A$2:$D$100, 2, FALSE)
Approximate Match vs Exact Match
The fourth argument controls how Excel searches for your value.
FALSE = Exact Match
Use this most of the time. Excel finds the exact value or returns an error. This is what you want for IDs, names, or codes.
TRUE = Approximate Match
Use this for range lookups, like tax brackets or commission tiers. Your table must be sorted in ascending order for this to work correctly.
Example of an approximate match use case:
| Score | Grade |
|---|---|
| 0 | F |
| 60 | D |
| 70 | C |
| 80 | B |
| 90 | A |
=VLOOKUP(85, $A$2:$B$6, 2, TRUE)
This returns “B” because 85 falls between 80 and 90. The table must be sorted for this to work right.
Common VLOOKUP Errors and How to Fix Them
#N/A Error
This means no match was found. Common causes:
- The lookup value does not exist in the table
- Extra spaces in the data (use TRIM to fix)
- Numbers stored as text (format cells as numbers)
- Typos in the lookup value
Fix it by wrapping your formula in IFERROR:
=IFERROR(VLOOKUP(A2, $B$2:$D$100, 2, FALSE), "Not Found")
#REF! Error
You picked a column number that is larger than the number of columns in your table. If your table has 3 columns and you wrote 5, you get this error. Count your columns and fix the number.
#VALUE! Error
The column index number is less than 1, or something non-numeric was used. Make sure your third argument is a number like 2 or 3.
Wrong Results Without Any Error
This usually happens when you use TRUE instead of FALSE, or when your data has formatting mismatches. Double-check the fourth argument and make sure your lookup column and lookup value are in the same format.
VLOOKUP Limitations You Should Know
VLOOKUP is powerful but it has real limitations:
- It can only look to the right. The result column must be to the right of the search column
- It returns the first match only. Duplicate values will be ignored after the first
- It is not dynamic. Adding columns to your table breaks the formula because column numbers shift
- Large datasets can make it slow
- It is case-insensitive. “APPLE” and “apple” are treated the same
For many of these problems, XLOOKUP is a better choice in 2026.
VLOOKUP vs XLOOKUP: What You Should Know
Microsoft introduced XLOOKUP as a modern replacement for VLOOKUP. Here is a quick comparison:
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Look left | No | Yes |
| Default match type | Approximate | Exact |
| Multiple columns returned | No | Yes |
| Error handling built in | No | Yes |
| Available in older Excel | Yes | No (2019 and earlier) |
If you are on Microsoft 365 or Excel 2021 and later, try XLOOKUP. The syntax is cleaner:
=XLOOKUP(A2, $B$2:$B$100, $D$2:$D$100, "Not Found")Tips to Make VLOOKUP Work Better
Use named ranges. Instead of writing $A$2:$D$100, name that range “ProductTable” and write:
=VLOOKUP(A2, ProductTable, 2, FALSE)
This makes formulas easier to read and manage.
Convert your data to a Table. Select your data and press Ctrl+T. Excel tables expand automatically, so your VLOOKUP range updates when you add new rows.
Use MATCH to make the column number dynamic. Hard-coding column numbers breaks when you insert columns. Use this instead:
=VLOOKUP(A2, $A$2:$D$100, MATCH("Price", $A$1:$D$1, 0), FALSE)
Now the formula finds the “Price” column by name, not by number.
Trim your data. Spaces before or after values cause #N/A errors. Run TRIM on your data or use it inside the formula:
=VLOOKUP(TRIM(A2), $B$2:$D$100, 2, FALSE)
For a deeper understanding of how lookup functions work and best practices for building reliable spreadsheets, the Chandoo Excel tutorials offer practical examples that go beyond the basics.
How to Use VLOOKUP with Two Conditions
VLOOKUP natively supports only one lookup value. To match on two columns, combine them with an ampersand.
Step 1: Add a helper column in your table that concatenates both values:
=A2&B2
This might give you something like “JohnSales” if A2 is a name and B2 is a department.
Step 2: Use the same concatenation as your lookup value:
=VLOOKUP(E2&F2, $C$2:$G$100, 3, FALSE)
This effectively creates a unique key from two columns.
Alternatively, use INDEX MATCH with multiple conditions, which is more flexible and does not require a helper column.
VLOOKUP Across Multiple Workbooks
To pull data from a different workbook:
- Open both workbooks
- Write the formula referencing the other file
- Excel will include the full path automatically
=VLOOKUP(A2, 'C:\Users\YourName\Documents\[Data.xlsx]Sheet1'!$A$2:$C$100, 2, FALSE)
When the source file is closed, Excel shows the full file path. The formula still works, but updating it requires the source file to be accessible.
Practice Exercise
Try this on your own:
- Create a sheet with two columns: Student ID (101, 102, 103) and Student Name (Alice, Bob, Carol)
- On a second sheet, type a Student ID in cell A1
- In cell B1, write a VLOOKUP to fetch the student name
- Test it with each ID
This small exercise will cement the concept better than reading alone.
Conclusion
VLOOKUP is one of the first functions worth mastering in Excel. It solves a real, everyday problem: matching data across tables. The key rules to remember are that your lookup value must be in the first column of your table, always use FALSE for exact matches, and lock your table range with dollar signs.
Once you are comfortable with VLOOKUP, explore XLOOKUP and INDEX MATCH. They are more flexible and will handle situations where VLOOKUP falls short.
Frequently Asked Questions
Can VLOOKUP look to the left?
No. VLOOKUP can only return values from columns to the right of your search column. If you need to look left, use XLOOKUP or the INDEX MATCH combination instead.
Why does my VLOOKUP return the wrong value?
The most common reason is using TRUE (approximate match) instead of FALSE (exact match). Also check for extra spaces in your data, mismatched number formats, and duplicate values in the lookup column.
What is the difference between VLOOKUP and INDEX MATCH?
VLOOKUP is simpler to write but has limitations. INDEX MATCH can look in any direction, handles column insertions without breaking, and is generally faster on large datasets. Both get the job done for basic tasks.
Can I use VLOOKUP with text values?
Yes. VLOOKUP works with text, numbers, and dates. Just make sure the format matches between your lookup value and the table. For example, if your table stores IDs as text but your lookup value is a number, you will get an #N/A error.
How do I stop VLOOKUP from returning an error when nothing is found?
Wrap your formula in IFERROR like this: =IFERROR(VLOOKUP(A2, $B$2:$D$100, 2, FALSE), “Not Found”). Replace “Not Found” with whatever you want to display when there is no match, including a blank: “”.
