The VLOOKUP function in Excel helps you search for information in a table and return matching data from another column. It is one of the most widely used Excel functions for looking up employee information, product prices, customer details, sales commissions, inventory data, and more. If you have ever needed Excel to find matching information instead of manually searching through rows, VLOOKUP is one of the best functions to learn.
In This Guide, You’ll Learn
- What the VLOOKUP function does
- The syntax and arguments of VLOOKUP
- How to use VLOOKUP for exact matches
- How to use VLOOKUP for approximate matches
- Common VLOOKUP mistakes and how to fix them
- Real-world business uses for VLOOKUP
What is the VLOOKUP Function?
The VLOOKUP function searches for a value in the first column of a table and returns related information from another column in the same row.
VLOOKUP Function Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Argument Definitions
- lookup_value – The value you want Excel to find in the first column of the table.
- table_array – The range of cells containing the lookup table.
- col_index_num – The column number containing the value you want returned.
- range_lookup – Optional argument that determines match type:
- FALSE = Exact match
- TRUE = Approximate match
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> VLOOKUP Function Data
Basic VLOOKUP Exact Match
Imagine you have an employee ID and want Excel to automatically return the employee’s department.
Formula Used
=VLOOKUP(1003,A2:C5,3,FALSE)Result
Excel searches for 1003 in the first column and returns the value from column 3: Finance.
Why This Works
- Excel searches column A for 1003
- It finds the matching row
- It returns the value from the 3rd column of the table
Using Cell References with VLOOKUP
Instead of typing the lookup value directly, you can reference a cell.
This is much more practical in real spreadsheets
Formula Used
=VLOOKUP(E2,A2:C5,2,FALSE)Result
Excel searches for the value in E2 (1002) in the first column and returns the value from column 2: Mike.
Returning Product Prices
VLOOKUP is commonly used to return prices from a product list.
Formula Used
=VLOOKUP(E2,A2:C5,3,FALSE)Result
Excel searches for the value in E2 (P102) in the first column and returns the value from column 3: $199.99.
Approximate Match VLOOKUP
Approximate match is commonly used for commission rates, tax brackets, or grading systems.
In approximate match mode, the lookup table must be sorted in ascending order.
Formula Used
=VLOOKUP(D2,A2:B5,2,TRUE)Result
Excel searches for the value in D2 (12000) in the first column and finds the closest lower value: 6%
Important Warning
When using TRUE, your lookup table must be sorted, otherwise, VLOOKUP may return incorrect results
Common VLOOKUP Mistakes
Getting a #N/A Error
This usually means Excel cannot find a match.
Common Causes
- Value does not exist in the table
- Spelling differences
- Extra spaces
- Wrong data type (text vs number)
Fix
Double-check your lookup value or use IFERROR.
Formula Used
=IFERROR(VLOOKUP(E2,A2:C5,3,FALSE),"Not Found")Result
Excel searches for the value in E2 (9999) in the first column and since it doesn’t not find it, Excel returns the IFERROR instructions: “Not Found”.
Wrong Column Number
If your col_index_num is incorrect, Excel returns the wrong information.
Incorrect:
=VLOOKUP(E2,A2:C5,5,FALSE)Why?
Your table only has 3 columns (A2:C5).
Approximate Match Errors
If you use TRUE with an unsorted table, the results may be wrong.
Always sort the lookup column from smallest to largest.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> VLOOKUP Function Data
