Introduction
The VLOOKUP function in Excel is one of the most widely used tools
for quickly finding and retrieving data from large tables. The term VLOOKUP
stands for Vertical Lookup, which means it searches for a value in the first
column of a table and then returns information from another column in the same
row.
This function is especially useful in workplaces where you deal
with employee records, product lists, sales reports, or any type of structured
data. Instead of manually scanning rows, VLOOKUP can instantly provide the
result you need. For example, if you know an employee’s ID, you can immediately
get their name, department, or salary without scrolling through hundreds of
rows.
The general syntax of the VLOOKUP function is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value → the value you want to find (e.g.,
employee ID).
- table_array → the range of data where the lookup will
happen.
- col_index_num → the column number (from the left) to
fetch the result from.
- [range_lookup] → optional; use FALSE for exact match,
TRUE for approximate match.
With this simple but powerful function, you can perform tasks
ranging from basic lookups to advanced data analysis by combining it with other
Excel functions.
In this tutorial, we’ll work with an employee dataset.
|
Emp ID |
Name |
Designation |
Department |
Joining
Date |
Regularization
Date |
Salary |
Remarks |
|
101 |
Ali Khan |
Analyst |
Finance |
1/1/2020 |
1/7/2020 |
55000 |
Good |
|
102 |
Sara
Ahmed |
Manager |
HR |
15/2/2019 |
15/8/2019 |
80000 |
Excellent |
|
103 |
Imran Ali |
Developer |
IT |
20/3/2021 |
20/9/2021 |
60000 |
Average |
|
104 |
Ayesha
Noor |
Accountant |
Finance |
10/1/2020 |
10/7/2020 |
50000 |
Good |
|
105 |
Bilal
Khan |
Designer |
Marketing |
25/4/2018 |
25/10/2018 |
45000 |
Good |
|
106 |
Fatima |
Coordinator |
HR |
18/5/2020 |
18/11/2020 |
40000 |
Average |
|
107 |
Hassan
Raza |
Senior
Dev |
IT |
2/6/2019 |
2/12/2019 |
75000 |
Excellent |
|
108 |
Zainab |
Analyst |
Finance |
11/7/2021 |
11/1/2022 |
56000 |
Good |
|
109 |
Kamran |
Manager |
Sales |
1/8/2018 |
1/2/2019 |
82000 |
Excellent |
|
110 |
Nadia |
Developer |
IT |
9/9/2020 |
9/3/2021 |
61000 |
Good |
|
111 |
Salman |
Accountant |
Finance |
14/10/2021 |
14/4/2022 |
48000 |
Average |
|
112 |
Maryam |
Designer |
Marketing |
21/11/2019 |
21/5/2020 |
46000 |
Good |
|
113 |
Ahmed
Raza |
Coordinator |
HR |
30/12/2020 |
30/6/2021 |
42000 |
Good |
|
114 |
Saba |
Analyst |
Finance |
5/1/2021 |
5/7/2021 |
57000 |
Excellent |
|
115 |
Usman |
Senior
Dev |
IT |
22/2/2019 |
22/8/2019 |
78000 |
Good |
Using this data, we’ll explore 12 powerful and practical VLOOKUP examples that range from basic to advanced.
Example 1: Quickly Find an
Employee’s Name from ID
Imagine you’re an HR assistant, and your manager asks you to pull
out the name of an employee from the payroll system using their ID. With
hundreds of employees listed, searching manually wastes time. VLOOKUP can
instantly fetch the name based on the employee’s ID.
Formula: =VLOOKUP(103, A2:H16, 2, FALSE)
Breakdown:
- 103 → The employee ID you’re searching for.
- A2:H16 → The employee data range.
- 2 → The second column in the dataset, which contains names.
- FALSE → Ensures you get an
exact match.
Result: For Emp ID 103, the result is Imran
Ali.
Why it matters: In offices with large employee lists, this approach saves hours and ensures accuracy when verifying names from IDs.
Example 2: Check Remarks of an
Employee with Approximate Match
Let’s say you’re auditing employee performance data. You don’t have
the exact ID but you want to see remarks close to a given ID. If employee IDs
are sorted, VLOOKUP can return the nearest match.
Formula: =VLOOKUP(105, A2:H16, 8, TRUE)
Breakdown:
- 105 → Lookup ID.
- 8 → Column 8 is “Remarks.”
- TRUE → Allows approximate
matching.
Result: For Emp ID 105, the formula returns
Good.
Why it matters: This is helpful in payroll or HR systems where IDs are sequential, and you want to quickly grab nearby employee information without needing exact data.
Example 3: Show Salary with a
Currency Symbol
When preparing salary slips or financial summaries, simply showing
numbers can look unprofessional. Adding a currency symbol makes reports
cleaner.
Formula: ="Rs. "&VLOOKUP(107, A2:H16, 7, FALSE)
Breakdown:
- VLOOKUP fetches the salary for Emp ID 107.
- The text "Rs. " adds
the currency symbol.
Result: For Emp ID 107, the result is Rs.
75000.
Why it matters: This technique improves reporting quality, making payroll sheets presentable to management.
Example 4: Confirm If an Employee
Exists or Not
Imagine a payroll officer checking whether an entered ID actually
exists. Normally, VLOOKUP returns #N/A if the ID isn’t found, which can confuse
non-technical staff. You can fix this with error handling.
Formula: =IFERROR(VLOOKUP(120, A2:H16, 2, FALSE),"Not Found")
Breakdown:
- If Emp ID 120 is found, VLOOKUP shows the employee’s name.
- If not found, IFERROR returns
the custom message Not Found.
Result: Since ID 120 does not exist, the
output is Not Found.
Why it matters: This improves usability and prevents confusion in official reports.
Example 5: Get the Salary of the
Employee with the Highest ID
Suppose the highest employee ID belongs to the most recent hire,
and you need to quickly check their salary. VLOOKUP can combine with the MAX
function for this.
Formula: =VLOOKUP(MAX(A2:A16), A2:H16, 7, FALSE)
Breakdown:
- MAX(A2:A16) finds the highest Emp ID.
- VLOOKUP returns the salary from
column 7 for that ID.
Result: Returns 78000, which is Usman’s
salary (Emp ID 115).
Why it matters: Quickly highlights the latest hire’s salary without manual searching.
Example 6: Lookup Salary
Dynamically Using Column Header
When working with reports that may change column order, hardcoding
column numbers breaks formulas. A more dynamic solution is combining VLOOKUP
with MATCH.
Formula: =VLOOKUP(107, A2:H16, MATCH("Salary", A1:H1, 0), FALSE)
Breakdown:
- MATCH("Salary", A1:H1, 0) finds the column position of Salary.
- VLOOKUP uses that dynamic
column number.
Result: For Emp ID 107, it returns 75000.
Why it matters: This approach makes formulas flexible and reliable even if column positions change.
Example 7: Identify the First
Employee in the Company
HR often needs to recognize the first employee in the system
(lowest Emp ID). Instead of manually scanning, Excel can do it in one step.
Formula: =VLOOKUP(MIN(A2:A16), A2:H16, 2, FALSE)
Breakdown:
- MIN(A2:A16) identifies the smallest ID.
- VLOOKUP returns the
corresponding employee’s name.
Result: Returns Ali Khan.
Why it matters: Helpful in employee recognition, long-service awards, or data audits.
Example 8: Find Employee Using
Partial Name
Imagine you remember only part of an employee’s name, like “Sar,”
but not the full spelling. Using wildcards, VLOOKUP can still find them.
Formula: =VLOOKUP("*Sar*", B2:H16, 2, FALSE)
Breakdown:
- *Sar* means find any name containing “Sar.”
- VLOOKUP searches column B for a
match.
Result: Returns Sara Ahmed.
Why it matters: Useful in large companies where recalling only partial information is common.
Example 9: Assign Salary Grades
Automatically
HR may want to assign salary grades such as A, B, C based on
ranges. You can do this using a helper table and nested VLOOKUP.
Helper Table:
|
Salary From |
Grade |
|
0 |
C |
|
50000 |
B |
|
75000 |
A |
Formula: =VLOOKUP(VLOOKUP(103, A2:H16, 7, FALSE), J2:K4, 2, TRUE)
Breakdown:
- Inner VLOOKUP finds salary of Emp ID 103.
- Outer VLOOKUP assigns grade
based on the helper table.
Result: Imran Ali earns 60000, so grade B.
Why it matters: Automates grading in performance reviews and salary analysis.
Example 10: Check Probation Status
Using Today’s Date
Regularization date determines whether an employee has completed
probation. Instead of manually checking, you can automate this.
Formula:
=IF(VLOOKUP(106, A2:H16, 6, FALSE)>TODAY(),"Probation","Regularized")
Breakdown:
- Retrieves Regularization Date.
- Compares it with today’s date.
- Returns “Probation” or
“Regularized.”
Result: Fatima’s status updates
automatically depending on today’s date.
Why it matters: Saves HR from manually tracking probation completion dates.
Example 11: Reverse Lookup – Find
Employee by Department
VLOOKUP usually works left-to-right, but what if you need to look
up an employee by department? With CHOOSE, you can rearrange columns.
Formula: =VLOOKUP("HR", CHOOSE({1,2}, D2:D16, B2:B16), 2, FALSE)
Breakdown:
- CHOOSE({1,2}, D2:D16, B2:B16) makes Department act as the first column.
- VLOOKUP searches for HR and
returns Name.
Result: Returns Sara Ahmed.
Why it matters: Enables reverse lookups, extending VLOOKUP’s power.
Example 12: Display Dates in a
Professional Format
Reports look unprofessional when dates appear as numbers (e.g.,
44402). Formatting them properly with TEXT makes reports cleaner.
Formula: =TEXT(VLOOKUP(108, A2:H16, 5, FALSE),"dd-mmm-yyyy")
Breakdown:
- Retrieves Joining Date.
- Formats it as 11-Jul-2021.
Result: Returns 11-Jul-2021.
Why it matters: Makes HR reports and employee records presentation-ready.
Conclusion
VLOOKUP may seem simple, but as we’ve seen across these 12
examples, it can handle everything from finding names and salaries to grading
employees and automating probation checks. By combining it with other functions
like IF, MATCH, MAX, MIN, CHOOSE, and TEXT, you unlock advanced capabilities
that make your Excel skills truly powerful. In any office role—whether in HR, payroll, or management—knowing
VLOOKUP can save time, reduce errors, and make data reporting more
professional.
0 Comments