VLOOKUP Function in Excel Tutorial

Excel Tutorial

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:

  1. Excel searches for the required value in the first column of the selected table.
  2. It identifies the row containing that value.
  3. It moves to the column number you specify.
  4. It returns the value from that position.

VLOOKUP Function Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Understanding the Arguments

ArgumentMeaning
lookup_valueThe value you want Excel to search for.
table_arrayThe complete table or range containing the lookup value and result columns.
col_index_numThe column number from which Excel should return the result.
range_lookupSpecifies 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 PositionColumn Name
1Employee ID
2Employee Name
3Department
4Salary

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.

ValueMeaning
FALSE or 0Finds an exact match.
TRUE or 1Finds 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 IDNameDepartmentSalary
E101RahulIT35000
E102PriyaHR40000
E103AmanSales45000
E104NehaFinance50000
E105KaranIT55000

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:

  1. "E103" is the Employee ID Excel needs to find.
  2. A2:D6 is the selected employee table.
  3. 4 tells Excel to return the value from the fourth column.
  4. FALSE tells Excel to find an exact match.
  5. 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 IDEmployee NameDepartment
E101RahulIT
E102PriyaHR
E103AmanSales
E104NehaFinance
E105KaranIT

Assume cell E2 contains E103.

Excel Formula

=VLOOKUP(E2,A2:C6,2,FALSE)

Expected Result

Employee IDEmployee Name
E103Aman

Explanation

  • E2 contains the Employee ID to search for.
  • A2:C6 is the employee table.
  • 2 tells Excel to return the value from the second column.
  • FALSE searches 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 IDEmployee NameDepartmentSalary
E101RahulIT35000
E102PriyaHR40000
E103AmanSales45000
E104NehaFinance50000
E105KaranIT55000

Excel Formula

=VLOOKUP("E104",A2:D6,4,FALSE)

Expected Result

Employee IDSalary
E10450000

Explanation

  • "E104" is the Employee ID to search for.
  • A2:D6 contains the employee records.
  • 4 selects the Salary column.
  • FALSE searches 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 IDProduct NameCategoryPrice
P101LaptopElectronics55000
P102KeyboardAccessories1200
P103MouseAccessories600
P104MonitorElectronics12000
P105PrinterElectronics8500

Assume cell F2 contains P104.

Excel Formula

=VLOOKUP(F2,A2:D6,4,FALSE)

Expected Result

Product IDPrice
P10412000

Explanation

  • F2 contains the Product ID.
  • A2:D6 is the product table.
  • 4 selects the fourth column, which contains Price.
  • FALSE searches 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 NumberStudent NameMathematicsEnglishScience
101Aarav857890
102Riya928895
103Mohit758280
104Simran899187
105Kabir807684

Excel Formula

=VLOOKUP(103,A2:E6,4,FALSE)

Expected Result

Roll NumberEnglish Marks
10382

Explanation

  • 103 is the Roll Number to search for.
  • A2:E6 contains the student records.
  • 4 selects the fourth column of the selected table, which contains English marks.
  • FALSE searches 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 SalesIncentive
02%
100003%
200005%
300007%
4000010%

Excel Formula

=VLOOKUP(35000,A2:B6,2,TRUE)

Expected Result

Sales AmountIncentive
350007%

Explanation

  • 35000 is the sales amount to search for.
  • A2:B6 contains the minimum sales and incentive table.
  • 2 selects the Incentive column.
  • TRUE tells 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 IDEmployee NameDepartmentCity
E201ArjunITDelhi
E202MeeraHRNoida
E203DevSalesGurgaon
E204PoojaFinanceDelhi
E205RohanMarketingNoida

Assume cell F2 contains E203.

Excel Formula

=VLOOKUP(F2,A2:D6,3,FALSE)

Expected Result

Employee IDDepartment
E203Sales

Explanation

  • F2 contains the Employee ID.
  • A2:D6 is the employee data range.
  • 3 selects the Department column.
  • FALSE searches 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 IDProduct NameSales
P301Laptop80000
P302Mobile55000
P303Tablet30000
P304Monitor45000
P305Printer25000

Assume cell E2 contains P301.

Excel Formula

=VLOOKUP(E2,$A$2:$C$6,3,FALSE)

Expected Result

Product IDSales
P30180000

Explanation

  • E2 contains the Product ID.
  • $A$2:$C$6 is the selected table range.
  • The dollar signs make the table reference absolute, so it remains fixed when the formula is copied.
  • 3 selects the Sales column.
  • FALSE searches 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 IDNameDepartment
E301AmitIT
E302RahulHR
E303NehaSales
E304PriyaFinance

Find the department of employee E303.

Task 2: Find a Product’s Price

Product IDProductPrice
P401Laptop55000
P402Mobile25000
P403Tablet18000
P404Printer12000

Find the price of product P402.

Task 3: Find Student Marks

Roll No.NameEnglishMathsScience
201Ankit758085
202Neha889290
203Rahul707882
204Simran958993

Retrieve the Science marks of Roll Number 204.

Task 4: Find an Incentive Percentage

Minimum SalesIncentive
02%
100003%
200005%
300007%
4000010%

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 IDNameDepartmentSalaryCity
E101RahulIT35000Delhi
E102PriyaHR40000Noida
E103AmanSales45000Gurgaon
E104NehaFinance50000Delhi
E105KaranIT55000Noida
E106SimranMarketing48000Gurgaon

Create a separate search area:

Search FieldValue
Employee IDE103
Employee Name
Department
Salary
City

Your Tasks

  1. Retrieve the employee’s name using Employee ID.
  2. Retrieve the employee’s department.
  3. Retrieve the employee’s salary.
  4. Retrieve the employee’s city.
  5. 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_value specifies what you want to find.
  • table_array defines the lookup table.
  • col_index_num identifies the column from which the result is returned.
  • FALSE is used for exact matching.
  • TRUE is 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.