Mastering VLOOKUP in Excel
Overview, Examples, and Assignments for Beginners
The VLOOKUP function
in Excel is used to search for a value in the first column of a table (or
range) and return a value in the same row from a specified column. This is
particularly useful for retrieving data from large datasets based on a key
value.
Syntax
The syntax of the
VLOOKUP function is as follows:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value:
The value you want to search for in the first column of your table.
- table_array:
The range of cells that contains the data. It should include the column
with the lookup_value and the column from which you want to retrieve data.
- col_index_num:
The column number in the table_array from which to retrieve the value. The
first column in the range is 1, the second is 2, and so on.
- [range_lookup]: An
optional argument that specifies whether you want an exact match or an
approximate match. Use FALSE for an exact match and TRUE (or omitted) for
an approximate match.
Example Scenario
Employee ID Name Department
101 | John Doe | Sales |
102 | Jane Smith | Marketing |
103 | Emily Davis | IT |
104 | Michael Brown | HR |
Step-by-Step Example
1. Set Up Your Data
Create a table in
Excel with the data mentioned above.
2. Use the VLOOKUP
Function
Suppose you want to
find out which department employee with ID 103 belongs to.
- Step
1: Select the cell where you want the result to
appear (e.g., cell D1).
- Step
2: Enter the formula:
=VLOOKUP(103, A2:C5, 3, FALSE)
Explanation of the
Formula:
- 103:
This is the lookup_value, which is the Employee ID you’re searching for.
- A2:C5:
This is the table_array, the range that contains the data (from the
Employee ID to the Department).
- 3:
This is the col_index_num, indicating that you want to return the value
from the third column of the specified range (the Department column).
- FALSE:
This indicates that you want an exact match for the Employee ID.
3. Result
After pressing Enter,
the cell will display IT, which is the department of the employee
with ID 103.
Important Notes
- Exact
vs. Approximate Match:
- Using
FALSE for the range_lookup parameter will give you an exact match. If
there is no exact match found, it will return an #N/A error.
- Using
TRUE or omitting the parameter will give an approximate match, which
works only if the first column of your table_array is sorted in ascending
order.
- Common
Errors:
- #N/A:
This means that the lookup_value was not found.
- #REF!:
This error occurs if the col_index_num is greater than the number of
columns in the table_array.
- #VALUE!:
This error occurs if the lookup_value is not a valid type.
Practical Applications
1.
Data Retrieval: Use VLOOKUP to pull data from large datasets, such as customer
databases, product lists, or employee records.
2.
Data Analysis: Combine VLOOKUP with other functions like IF, SUM, or AVERAGE to
perform more complex calculations and analyses.
Conclusion
The VLOOKUP function is a powerful tool in Excel that simplifies the process of data retrieval.
Assignments
Assignment 1: Employee Department Lookup
Task: Given a table of employee data, use VLOOKUP to find the department of
a specific employee.
Sample Data
|
Employee ID |
Name |
Department |
|
101 |
John Doe |
Sales |
|
102 |
Jane Smith |
Marketing |
|
103 |
Emily Davis |
IT |
|
104 |
Michael Brown |
HR |
|
105 |
Sarah Johnson |
Finance |
Questions:
1.
What is the department of the employee
with ID 102?
2.
What is the department of the employee
with ID 105?
Solutions
1.
For ID 102:
o
Formula:
=VLOOKUP(102, A2:C6, 3, FALSE)
o
Result: Marketing
2.
For ID 105:
o
Formula:
=VLOOKUP(105, A2:C6, 3, FALSE)
o
Result: Finance
Assignment 2: Product Price Lookup
Task: Using the product pricing table, find the price of specific products
using VLOOKUP.
Sample Data
|
Product ID |
Product Name |
Price |
|
P001 |
Laptop |
$800 |
|
P002 |
Smartphone |
$600 |
|
P003 |
Tablet |
$300 |
|
P004 |
Headphones |
$150 |
|
P005 |
Monitor |
$200 |
Questions:
1.
What is the price of the product with
ID P003?
2.
What is the price of the product with
ID P001?
Solutions
1.
For ID P003:
o
Formula:
=VLOOKUP("P003", A2:C6, 3, FALSE)
o
Result: $300
2.
For ID P001:
o
Formula:
=VLOOKUP("P001", A2:C6, 3, FALSE)
o
Result: $800
Assignment 3: Student Grades Lookup
Task: Given a table of student grades, use VLOOKUP to find the grade of a
specific student.
Sample Data
|
Student ID |
Student Name |
Grade |
|
001 |
Alice Johnson |
A |
|
002 |
Bob Smith |
B |
|
003 |
Charlie Brown |
C |
|
004 |
David Wilson |
B |
|
005 |
Eva Adams |
A |
Questions:
1.
What grade did student 002 receive?
2.
What grade did student 004 receive?
Solutions
1.
For student ID 002:
o
Formula:
=VLOOKUP("002", A2:C6, 3, FALSE)
o
Result: B
2.
For student ID 004:
o
Formula:
=VLOOKUP("004", A2:C6, 3, FALSE)
o
Result: B
Assignment 4: Sales Data Lookup
Task: Use the sales data to find the total sales for specific products.
Sample Data
|
Product Code |
Product Name |
Total Sales |
|
A101 |
Widget A |
$1,500 |
|
A102 |
Widget B |
$2,200 |
|
A103 |
Widget C |
$750 |
|
A104 |
Widget D |
$3,100 |
|
A105 |
Widget E |
$850 |
Questions:
1.
What are the total sales for A102?
2.
What are the total sales for A105?
Solutions
1.
For product code A102:
o
Formula:
=VLOOKUP("A102", A2:C6, 3, FALSE)
o
Result: $2,200
2.
For product code A105:
o
Formula:
=VLOOKUP("A105", A2:C6, 3, FALSE)
o
Result: $850
Assignment 5: Customer Contact Lookup
Task: Use the customer contact table to find the email address of specific
customers.
Sample Data
|
Customer ID |
Customer Name |
Email |
|
C001 |
John Green |
john.green@example.com |
|
C002 |
Mary White |
mary.white@example.com |
|
C003 |
Jake Black |
jake.black@example.com |
|
C004 |
Emma Watson |
emma.watson@example.com |
|
C005 |
Liam Brown |
liam.brown@example.com |
Questions:
1.
What is the email address of customer
C002?
2.
What is the email address of customer
C004?
Solutions
1.
For customer ID C002:
o
Formula:
=VLOOKUP("C002", A2:C6, 3, FALSE)
o
Result: mary.white@example.com
2.
For customer ID C004:
o
Formula:
=VLOOKUP("C004", A2:C6, 3, FALSE)
o
Result: emma.watson@example.com
Assignment 6: Book Inventory Lookup
Task: Use the book inventory table to find the price and author of specific
books.
Sample Data
ISBN
Title
Author
Price
Questions: 1.
What is the author of the book with
ISBN 978-1-23456-788-0? 2.
What is the price of the book titled
"The Great Adventure"? Solutions 1.
For ISBN 978-1-23456-788-0: o
Formula: =VLOOKUP("978-1-23456-788-0", A2:D6, 3, FALSE) o
Result: Jane Doe 2.
For the title "The Great
Adventure": o
Formula: =VLOOKUP("The Great Adventure", B2:D6, 4, FALSE) o
Result: $15.99 Assignment 7: Course
Enrollment Lookup Task: Using the course enrollment data, find the instructor for a specific
course. Sample Data
Questions: 1.
Who is the instructor for the course
code BUS202? 2.
Who teaches the course titled
"Microeconomics"? Solutions 1.
For course code BUS202: o
Formula: =VLOOKUP("BUS202", A2:C6, 3, FALSE) o
Result: Prof. Bob Smith 2.
For the course titled
"Microeconomics": o
Formula: =VLOOKUP("Microeconomics", B2:C6, 2, FALSE) o
Result: Dr. Charlie Brown Assignment 8: Client Information LookupTask: Find the contact number of specific clients from the client
information table. Sample Data
Questions: 1.
What is the contact number for client
ID CL003? 2.
What is the contact number for client
ID CL001? Solutions 1.
For client ID CL003: o
Formula: =VLOOKUP("CL003", A2:C6, 3, FALSE) o
Result: 555-0789 2.
For client ID CL001: o
Formula: =VLOOKUP("CL001", A2:C6, 3, FALSE) o
Result: 555-0123 Assignment 9: Employee Salary LookupTask: Use the employee salary table to find the salary of specific
employees. Sample Data
Questions: 1.
What is the salary of employee ID 102? 2.
What is the salary of employee ID 105? Solutions 1.
For employee ID 102: o
Formula: =VLOOKUP(102, A2:C6, 3, FALSE) o
Result: $75,000 2.
For employee ID 105: o
Formula: =VLOOKUP(105, A2:C6, 3, FALSE) o
Result: $55,000 Assignment 10: Flight Information LookupTask: Use the flight information table to find the destination of specific
flights. Sample Data
Questions: 1.
What is the destination for flight
DL202? 2.
What is the destination for flight
UA303? Solutions 1.
For flight number DL202: o
Formula: =VLOOKUP("DL202", A2:C6, 3, FALSE) o
Result: Los Angeles 2.
For flight number UA303: o
Formula: =VLOOKUP("UA303", A2:C6, 3, FALSE) o
Result: Chicago | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
