VLOOKUP is one of the most commonly used lookup functions in Microsoft Excel. It helps you find a value in a table and return related information from another column.
For example, you can use VLOOKUP to find an employee's department using an Employee ID, find a product price using a Product ID, or retrieve a customer's email address from a customer list.
In this guide, you'll learn the VLOOKUP formula in Excel, how the formula works, how to use it with examples, how to look up data between two sheets, and how to fix common VLOOKUP errors.
What Is VLOOKUP in Excel?
VLOOKUP stands for Vertical Lookup.
The VLOOKUP function searches for a value in the first column of a table and returns a corresponding value from another column in the same row.
A simple example is an employee database:
| Employee ID | Name | Department | Salary |
|---|---|---|---|
| E101 | Rahul | Sales | 35000 |
| E102 | Priya | HR | 40000 |
| E103 | Amit | IT | 45000 |
E102 into another cell and want Excel to return the employee's department.
VLOOKUP can automatically find E102 and return HR.
VLOOKUP Formula Syntax
The basic VLOOKUP formula is:
excel=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The four arguments are:
lookup_value – The value you want to find.
table_array – The range containing the lookup data.
col_index_num – The column number containing the value you want to return.
range_lookup – Determines whether Excel should perform an exact or approximate match.
For most everyday lookups, use FALSE for an exact match.
Simple VLOOKUP Formula Example
Suppose your data is in cells A2:D4:
| Employee ID | Name | Department | Salary |
|---|---|---|---|
| E101 | Rahul | Sales | 35000 |
| E102 | Priya | HR | 40000 |
| E103 | Amit | IT | 45000 |
F2 contains E102, you can use:
excel=VLOOKUP(F2,A2:D4,3,FALSE)
Excel searches for E102 in the first column of A2:D4.
The third column is Department, so the result is:
textHR
How the VLOOKUP Formula Works
Consider:
excel=VLOOKUP(F2,A2:D4,3,FALSE)
Excel performs four steps:
F2.A2:D4.This is why understanding the table array and column index number is important.
What Is the Table Array in VLOOKUP?
The table_array is the range of cells where Excel searches for the lookup value and retrieves the result.
For example:
excelA2:D100
The first column of this range must contain the value you want VLOOKUP to search for.
For example:
excel=VLOOKUP(F2,A2:D100,3,FALSE)
Here:
* F2 = lookup value
* A2:D100 = table array
* 3 = return the third column
* FALSE = exact match
What Is the Column Index Number in VLOOKUP?
The col_index_num tells Excel which column to return.
For this table:
| A | B | C | D |
|---|---|---|---|
| Employee ID | Name | Department | Salary |
* Employee ID = 1
* Name = 2
* Department = 3
* Salary = 4
Therefore:
excel=VLOOKUP(F2,A2:D100,2,FALSE)
returns the Name.
And:
excel=VLOOKUP(F2,A2:D100,4,FALSE)
returns the Salary.
Exact Match vs Approximate Match
The last argument of VLOOKUP controls the type of match.
Exact match
Use:
excelFALSE
or:
excel0
Example:
excel=VLOOKUP(F2,A2:D100,3,FALSE)
This searches for an exact match.
Exact matching is usually the safest choice for IDs, names, product codes, order numbers, and other unique values.
Approximate match
Use:
excelTRUE
or:
excel1
Example:
excel=VLOOKUP(F2,A2:B10,2,TRUE)
Approximate matching is commonly used for ranges such as grades, commission rates, tax brackets, or pricing tiers.
The lookup column generally needs to be sorted correctly for approximate matching.
VLOOKUP Between Two Excel Sheets
You can use VLOOKUP to retrieve information from another worksheet.
Suppose Sheet1 contains:
| Employee ID | Name |
|---|---|
| E101 | Rahul |
| E102 | Priya |
Sheet2 contains:
| Employee ID | Department |
|---|---|
| E101 | Sales |
| E102 | HR |
Sheet2, you could use:
excel=VLOOKUP(A2,Sheet2!A:B,2,FALSE)
This searches for the Employee ID from A2 in Sheet2 and returns the value from its second column.
VLOOKUP Between Two Excel Files
VLOOKUP can also retrieve data from another Excel workbook.
For example, one workbook might contain customer IDs while another contains customer contact information.
A formula can reference the other workbook, but maintaining external workbook references can become inconvenient when working with large files or repeatedly matching data.
For users who need to match data without writing formulas, an online lookup tool can automate this process.
Try the free Excel VLOOKUP online tool to match data between Excel or CSV files without manually creating VLOOKUP formulas.
How to Return Multiple Columns With VLOOKUP
You can use VLOOKUP several times to retrieve different columns.
For example:
excel=VLOOKUP(F2,A2:D100,2,FALSE)
returns the Name.
excel=VLOOKUP(F2,A2:D100,3,FALSE)
returns the Department.
excel=VLOOKUP(F2,A2:D100,4,FALSE)
returns the Salary.
This works well for smaller tasks, but large lookups can require many formulas.
VLOOKUP With IFERROR
When VLOOKUP cannot find the lookup value, Excel returns #N/A.
You can use IFERROR to display a more useful result.
For example:
excel=IFERROR(VLOOKUP(F2,A2:D100,3,FALSE),"Not Found")
Instead of displaying:
text#N/A
Excel displays:
textNot Found
VLOOKUP With Multiple Criteria
Standard VLOOKUP searches using a single lookup value.
When you need to match multiple conditions, you may need helper columns, combined lookup keys, XLOOKUP, INDEX/MATCH, or other Excel techniques.
For example, you might need to match both:
* Employee ID
* Date
before returning a result.
For advanced lookup problems, choosing the correct lookup method is important because a simple VLOOKUP formula may not be sufficient.
Common VLOOKUP Errors
#N/A
Usually means Excel cannot find the lookup value.
Check for:
* spelling differences
* leading or trailing spaces
* numbers stored as text
* incorrect lookup range
* incorrect match type
#REF!
This usually occurs when the column index number is greater than the number of columns in the table array.
For example:
excel=VLOOKUP(F2,A2:D100,5,FALSE)
The table only has four columns, so column 5 is invalid.
#VALUE!
This can occur when one of the formula arguments is invalid or has an unexpected value.
Wrong result
Check whether you used:
excelTRUE
when you actually needed:
excelFALSE
For most exact ID-based lookups, FALSE is the appropriate choice.
VLOOKUP Not Working? Try These Fixes
When VLOOKUP isn't working correctly, check the following:
FALSE for exact matching when appropriate.VLOOKUP vs XLOOKUP
XLOOKUP is a newer lookup function available in newer versions of Excel.
Compared with VLOOKUP, XLOOKUP provides more flexibility and can look in different directions.
However, VLOOKUP remains extremely common because it is supported by many Excel versions and is familiar to millions of users.
For simple vertical lookups, VLOOKUP is often easy to understand and use.
VLOOKUP vs HLOOKUP
VLOOKUP searches vertically, while HLOOKUP searches horizontally.
VLOOKUP
Searches the first column and returns a value from another column.
HLOOKUP
Searches the first row and returns a value from another row.
For most modern Excel workflows, XLOOKUP is also worth considering.
How to Use VLOOKUP Without Writing a Formula
VLOOKUP is useful, but manually creating formulas can become tedious when you need to match large Excel files or repeatedly combine data.
For example, suppose you have:
File 1
| Employee ID | Name |
|---|---|
| E101 | Rahul |
| E102 | Priya |
File 2
| Employee ID | Department | Salary |
|---|---|---|
| E101 | Sales | 35000 |
| E102 | HR | 40000 |
Use the free Excel VLOOKUP tool to match Excel or CSV data without creating formulas manually.
Frequently Asked Questions
What is the VLOOKUP formula in Excel?
The basic VLOOKUP formula is:
excel=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
It searches for a value in the first column of a table and returns a corresponding value from another column.
What is the easiest VLOOKUP formula?
For an exact match, a common formula is:
excel=VLOOKUP(A2,A:B,2,FALSE)
Can VLOOKUP work between two sheets?
Yes. You can reference another worksheet in the table array, for example:
excel=VLOOKUP(A2,Sheet2!A:B,2,FALSE)
Can VLOOKUP work between two Excel files?
Yes. Excel can create external workbook references for VLOOKUP. However, an online lookup tool can be easier when you simply need to match and combine two files.
Why does VLOOKUP return #N/A?
Usually, the lookup value cannot be found in the first column of the table array. Formatting differences, extra spaces, and numbers stored as text can also cause problems.
Can VLOOKUP return multiple columns?
Yes, but you generally need separate VLOOKUP formulas for each column you want to retrieve.
Is VLOOKUP case-sensitive?
No. Standard VLOOKUP does not distinguish between uppercase and lowercase letters.
Is XLOOKUP better than VLOOKUP?
XLOOKUP is more flexible and is preferable when available, but VLOOKUP is still widely used and remains useful for many common lookup tasks.
Conclusion
VLOOKUP is a powerful Excel function for finding information and retrieving related values from a table. Understanding the VLOOKUP formula, table array, column index number, and exact-match option makes it much easier to work with Excel data.
For simple lookups, a formula such as:
excel=VLOOKUP(A2,A:B,2,FALSE)
may be all you need.
When you need to match large Excel or CSV files without manually creating formulas, you can use the free Excel VLOOKUP online tool from ExcelMergeOnline.
