Introduction
The VLOOKUP function in Excel helps you search for a value in the first column of a table and retrieve related information from another column in the same row. It is commonly used in employee records, product lists, student marksheets, and sales reports. Let’s understand VLOOKUP with simple explanations and practical examples.
What Is the VLOOKUP Function in Excel?
VLOOKUP stands for Vertical Lookup.
It searches for a value vertically in the first column of a selected table and returns information from another column in the same row.
For example, suppose an employee table contains Employee ID, Name, Department, and Salary.
If you enter an Employee ID, VLOOKUP can search for that ID and return the employee’s salary.
In simple words:
- Excel searches for the required value in the first column of the selected table.
- It identifies the row containing that value.
- It moves to the column number you specify.
- It returns the value from that position.
VLOOKUP Function Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Understanding the Arguments
| Argument | Meaning |
|---|---|
| lookup_value | The value you want Excel to search for. |
| table_array | The complete table or range containing the lookup value and result columns. |
| col_index_num | The column number from which Excel should return the result. |
| range_lookup | Specifies whether you want an exact or approximate match. |
Understanding Each Argument
1. lookup_value
This is the value you want to find.
For example, if you want to find an employee’s salary using Employee ID E103, then E103 is your lookup_value.
2. table_array
This is the range containing your complete lookup table.
The first column of this selected range must contain the value you want to search for.
For example, if your table is in A2, the first column is A and VLOOKUP searches for the lookup value in that column.
3. col_index_num
This argument tells Excel which column contains the result you want.
Suppose your selected table contains four columns:
| Column Position | Column Name |
|---|---|
| 1 | Employee ID |
| 2 | Employee Name |
| 3 | Department |
| 4 | Salary |
If you want to retrieve Salary, use column number 4.
Important: VLOOKUP counts columns from the beginning of the selected table, not from the worksheet’s actual column letters.
4. range_lookup
This argument controls the type of matching.
| Value | Meaning |
|---|---|
| FALSE or 0 | Finds an exact match. |
| TRUE or 1 | Finds an approximate match. |
For most employee, product, and student lookup tasks, use FALSE because you normally want an exact match.
If you omit this argument, Excel uses approximate matching by default.
How Does the VLOOKUP Function Work?
Suppose a company maintains the following employee records:
| Employee ID | Name | Department | Salary |
|---|---|---|---|
| E101 | Rahul | IT | 35000 |
| E102 | Priya | HR | 40000 |
| E103 | Aman | Sales | 45000 |
| E104 | Neha | Finance | 50000 |
| E105 | Karan | IT | 55000 |
The HR manager wants to find the salary of employee E103.
Use this formula:
=VLOOKUP("E103",A2:D6,4,FALSE)
Let’s understand how Excel evaluates the formula:
"E103"is the Employee ID Excel needs to find.A2:D6is the selected employee table.4tells Excel to return the value from the fourth column.FALSEtells Excel to find an exact match.- Excel finds E103 in the third data row and returns the salary from that row.
<escape>Expected Result: 45000</escape>
The result is 45000 because the salary of employee E103 is 45000.
Practical Examples of VLOOKUP Function in Excel
Let’s practice VLOOKUP with different real-world situations.
Question 1: Find an Employee’s Name Using Employee ID
Problem Statement
An HR department maintains employee records. The manager wants to enter an Employee ID and retrieve the employee’s name.
Data Table
| Employee ID | Employee Name | Department |
|---|---|---|
| E101 | Rahul | IT |
| E102 | Priya | HR |
| E103 | Aman | Sales |
| E104 | Neha | Finance |
| E105 | Karan | IT |
Assume cell E2 contains E103.
Excel Formula
=VLOOKUP(E2,A2:C6,2,FALSE)
Expected Result
| Employee ID | Employee Name |
|---|---|
| E103 | Aman |
Explanation
E2contains the Employee ID to search for.A2:C6is the employee table.2tells Excel to return the value from the second column.FALSEsearches for an exact Employee ID.- Excel finds E103 and returns Aman.
Concept Covered
Retrieving employee names using Employee IDs and cell references.
Question 2: Find an Employee’s Salary
Problem Statement
The HR manager wants to retrieve the salary of employee E104 from the employee records.
Data Table
| Employee ID | Employee Name | Department | Salary |
|---|---|---|---|
| E101 | Rahul | IT | 35000 |
| E102 | Priya | HR | 40000 |
| E103 | Aman | Sales | 45000 |
| E104 | Neha | Finance | 50000 |
| E105 | Karan | IT | 55000 |
Excel Formula
=VLOOKUP("E104",A2:D6,4,FALSE)
Expected Result
| Employee ID | Salary |
|---|---|
| E104 | 50000 |
Explanation
"E104"is the Employee ID to search for.A2:D6contains the employee records.4selects the Salary column.FALSEsearches for an exact match.- Excel returns 50000.
Concept Covered
Retrieving numerical information from a selected column using an exact lookup.
Question 3: Find a Product’s Price
Problem Statement
An electronics store maintains product information. The manager wants to find the price of a product using its Product ID.
Data Table
| Product ID | Product Name | Category | Price |
|---|---|---|---|
| P101 | Laptop | Electronics | 55000 |
| P102 | Keyboard | Accessories | 1200 |
| P103 | Mouse | Accessories | 600 |
| P104 | Monitor | Electronics | 12000 |
| P105 | Printer | Electronics | 8500 |
Assume cell F2 contains P104.
Excel Formula
=VLOOKUP(F2,A2:D6,4,FALSE)
Expected Result
| Product ID | Price |
|---|---|
| P104 | 12000 |
Explanation
F2contains the Product ID.A2:D6is the product table.4selects the fourth column, which contains Price.FALSEsearches for an exact Product ID.- Excel returns 12000.
Concept Covered
Using VLOOKUP with a cell reference to retrieve product prices.
Question 4: Find a Student’s Marks
Problem Statement
A school maintains student marks in different subjects. The teacher wants to retrieve the English marks of a student using the student’s Roll Number.
Data Table
| Roll Number | Student Name | Mathematics | English | Science |
|---|---|---|---|---|
| 101 | Aarav | 85 | 78 | 90 |
| 102 | Riya | 92 | 88 | 95 |
| 103 | Mohit | 75 | 82 | 80 |
| 104 | Simran | 89 | 91 | 87 |
| 105 | Kabir | 80 | 76 | 84 |
Excel Formula
=VLOOKUP(103,A2:E6,4,FALSE)
Expected Result
| Roll Number | English Marks |
|---|---|
| 103 | 82 |
Explanation
103is the Roll Number to search for.A2:E6contains the student records.4selects the fourth column of the selected table, which contains English marks.FALSEsearches for an exact Roll Number.- Excel returns 82.
Concept Covered
Retrieving a particular subject’s marks from a student record using a unique Roll Number.
Question 5: Understand Approximate Match in VLOOKUP
Problem Statement
A company provides incentives based on employee sales. The incentive percentage depends on the sales amount.
The manager wants to find the applicable incentive percentage for an employee who has achieved sales of 35000.
Data Table
| Minimum Sales | Incentive |
|---|---|
| 0 | 2% |
| 10000 | 3% |
| 20000 | 5% |
| 30000 | 7% |
| 40000 | 10% |
Excel Formula
=VLOOKUP(35000,A2:B6,2,TRUE)
Expected Result
| Sales Amount | Incentive |
|---|---|
| 35000 | 7% |
Explanation
35000is the sales amount to search for.A2:B6contains the minimum sales and incentive table.2selects the Incentive column.TRUEtells Excel to use approximate matching.- Excel finds the largest minimum sales value that is less than or equal to 35000. That value is 30000, so the result is 7%.
Concept Covered
Using approximate matching to find the applicable rate from a threshold table.
Important: For approximate matching with TRUE, the first column of the selected table must be sorted in ascending order.
Question 6: Find a Department Using an Employee ID
Problem Statement
A company maintains employee details. The manager wants to retrieve an employee’s department by entering the Employee ID in a separate cell.
Data Table
| Employee ID | Employee Name | Department | City |
|---|---|---|---|
| E201 | Arjun | IT | Delhi |
| E202 | Meera | HR | Noida |
| E203 | Dev | Sales | Gurgaon |
| E204 | Pooja | Finance | Delhi |
| E205 | Rohan | Marketing | Noida |
Assume cell F2 contains E203.
Excel Formula
=VLOOKUP(F2,A2:D6,3,FALSE)
Expected Result
| Employee ID | Department |
|---|---|
| E203 | Sales |
Explanation
F2contains the Employee ID.A2:D6is the employee data range.3selects the Department column.FALSEsearches for an exact match.- Excel returns Sales.
Concept Covered
Retrieving different employee details by changing the lookup value in a cell.
Question 7: Retrieve a Value from a Fixed Table Range
Problem Statement
A sales department maintains product sales records. The manager wants to retrieve the sales amount of Laptop using its Product ID.
The manager also wants the lookup table range to remain fixed when copying the formula to another cell.
Data Table
| Product ID | Product Name | Sales |
|---|---|---|
| P301 | Laptop | 80000 |
| P302 | Mobile | 55000 |
| P303 | Tablet | 30000 |
| P304 | Monitor | 45000 |
| P305 | Printer | 25000 |
Assume cell E2 contains P301.
Excel Formula
=VLOOKUP(E2,$A$2:$C$6,3,FALSE)
Expected Result
| Product ID | Sales |
|---|---|
| P301 | 80000 |
Explanation
E2contains the Product ID.$A$2:$C$6is the selected table range.- The dollar signs make the table reference absolute, so it remains fixed when the formula is copied.
3selects the Sales column.FALSEsearches for an exact match.- Excel returns 80000.
Concept Covered
Using an absolute table reference in VLOOKUP so the lookup range does not shift when copying the formula.
Student Practice Tasks
Try these tasks yourself using the VLOOKUP function.
Task 1: Find an Employee’s Department
| Employee ID | Name | Department |
|---|---|---|
| E301 | Amit | IT |
| E302 | Rahul | HR |
| E303 | Neha | Sales |
| E304 | Priya | Finance |
Find the department of employee E303.
Task 2: Find a Product’s Price
| Product ID | Product | Price |
|---|---|---|
| P401 | Laptop | 55000 |
| P402 | Mobile | 25000 |
| P403 | Tablet | 18000 |
| P404 | Printer | 12000 |
Find the price of product P402.
Task 3: Find Student Marks
| Roll No. | Name | English | Maths | Science |
|---|---|---|---|---|
| 201 | Ankit | 75 | 80 | 85 |
| 202 | Neha | 88 | 92 | 90 |
| 203 | Rahul | 70 | 78 | 82 |
| 204 | Simran | 95 | 89 | 93 |
Retrieve the Science marks of Roll Number 204.
Task 4: Find an Incentive Percentage
| Minimum Sales | Incentive |
|---|---|
| 0 | 2% |
| 10000 | 3% |
| 20000 | 5% |
| 30000 | 7% |
| 40000 | 10% |
Find the incentive percentage for sales of 25000 using approximate matching.
Final Student Challenge: Build an Employee Information Finder
Create the following employee table in Excel.
| Employee ID | Name | Department | Salary | City |
|---|---|---|---|---|
| E101 | Rahul | IT | 35000 | Delhi |
| E102 | Priya | HR | 40000 | Noida |
| E103 | Aman | Sales | 45000 | Gurgaon |
| E104 | Neha | Finance | 50000 | Delhi |
| E105 | Karan | IT | 55000 | Noida |
| E106 | Simran | Marketing | 48000 | Gurgaon |
Create a separate search area:
| Search Field | Value |
|---|---|
| Employee ID | E103 |
| Employee Name | |
| Department | |
| Salary | |
| City |
Your Tasks
- Retrieve the employee’s name using Employee ID.
- Retrieve the employee’s department.
- Retrieve the employee’s salary.
- Retrieve the employee’s city.
- Change the Employee ID and check whether all returned details change according to the selected employee.
Challenge Goal: Build a simple employee information finder using VLOOKUP and cell references. This exercise will help you understand how lookup tables are used in real office reports.
Key Takeaways
- VLOOKUP stands for Vertical Lookup.
- It searches for a value in the first column of the selected table.
- It returns information from another column in the same row.
lookup_valuespecifies what you want to find.table_arraydefines the lookup table.col_index_numidentifies the column from which the result is returned.FALSEis used for exact matching.TRUEis used for approximate matching and requires ascending-sorted lookup data.- VLOOKUP cannot directly return a value from a column to the left of the lookup column.
- VLOOKUP is useful for employee records, product lists, student marksheets, and sales reports.
Frequently Asked Questions (FAQs)
1. What is the VLOOKUP function in Excel?
VLOOKUP is an Excel function that searches for a value in the first column of a table and returns a related value from another column in the same row.
2. What does VLOOKUP stand for?
VLOOKUP stands for Vertical Lookup because it searches vertically down the first column of a selected table.
3. What is the syntax of VLOOKUP?
The syntax is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
4. What is the difference between TRUE and FALSE in VLOOKUP?
FALSE searches for an exact match, while TRUE searches for an approximate match. For approximate matching, the first column must be sorted in ascending order.
5. Why is the first column important in VLOOKUP?
VLOOKUP always searches for the lookup value in the first column of the selected table. If the lookup value is not in that column, VLOOKUP cannot directly find it.
6. Can VLOOKUP return values from the left side?
No. Traditional VLOOKUP can only return values from columns to the right of the first column in the selected table.
7. What does col_index_num mean in VLOOKUP?
It specifies the position of the column from which Excel should return the result. The counting starts from the first column of the selected table.
8. Why does VLOOKUP return #N/A?
VLOOKUP returns #N/A when it cannot find the required value using the specified matching method. In exact-match searches, check whether the lookup value exists in the first column and whether the values are stored in compatible formats.
9. Can VLOOKUP search using a cell reference?
Yes. You can enter the lookup value in a cell and use that cell reference as the first argument. This makes it easier to change the search value.
10. What is the difference between VLOOKUP and MATCH?
VLOOKUP searches for a value in the first column of a table and returns a related value from another column. MATCH searches for a value and returns its relative position in a selected range.

