Excel VLOOKUP Exercises
Problem 01 Find Salary of an Employee:
Name Department Salary
Ross Sales $25,000
Helen Sales $30,000
Tamara Accounting $19,000
Zoey Marketing $23,000
Jackson Accounting $30,000
Lehman Sales $27,000 Problem 01 Find
from the dataset.
Problem 02 Find
Employee Salary salary and the de
Ross Problem 03 VLOO
starts with “Ro”.
Problem 04 Retu
given for a range
Problem 02 Find Salary and Department of an Employee: Problem 05 Grou
sort the ages into
Name Department Salary Problem 06 Retu
Ross Sales $25,000 is to find the valu
Helen Sales $30,000
use a helper colu
Problem 07 Nest
Tamara Accounting $19,000 related tables. Th
Zoey Marketing $23,000 second table. Use
Problem 08 Find
Jackson Accounting $30,000 the duplicate stat
Lehman Sales $27,000 Problem 09 Cond
contain marks fro
examination. Hig
Employee Salary
Ross
Problem 03 VLOOKUP with Partial Match:
Name Department Salary
Ross Sales $25,000
Helen Sales $30,000
Tamara Accounting $19,000
Zoey Marketing $23,000
Jackson Accounting $30,000
Lehman Sales $27,000
Employee Salary
Ro
Problem 04 Return Value from a Range:
Price Range Min PMT
0-$10,000 $0 $400
$10,000-$20,000 $10,001 $600
$20,000-$30,000 $20,001 $1,000
$30,000-$40,000 $30,001 $12,000
$40,000-$50,000 $40,001 $14,000
$50,000-$1,00,000 $50,001 $16,000
Purchase Price PMT
$27,000
Problem 05 Group Ages in Range:
Age Age Group
0 0-15
16 16-30
31 31-45
46 46-60
61 60+
Name Age Age Group
Ross 28
Helen 12
Tamara 29
Zoey 54
Jackson 32
Lehman 45
Problem 06 Return Second Match:
Helper Column Salesman Product Sales
Wilham Cable $2,600
Simon AC $11,500
Wilham TV $13,500
Nathan Fridge $17,000
Simon Router $5,500
Wilham Oven $10,000
Salesman Sales
Wilham2
Problem 07 Nested VLOOKUP:
ID Product Product Price
A101 Football Football $50
A102 Cricket Ball Cricket Ball $100
A103 Tennis Ball Tennis Ball $80
A104 Volleyball Volleyball $75
A105 Basketball Basketball $35
ID Price
A101
Problem 08 Find Duplicates:
Salesperson State 01 State 02 Duplicate
Peter Arizona New York
Ron Nevada Connecticut
Sam Alaska Minnesota
Roth Florida Nevada
Kyle Michigan Ohio
Fred New York Alaska
Problem 09 Conditional Formatting with VLOOKUP:
Name Score Name Retake
Ricky 90 Ricky 90
Mike 25 Mike 52
Adam 80 Adam 80
Ben 70 Ben 70
Alan 50 Alan 50
Leonardo 100 Leonardo 100
Hannah 30 Hannah 61
Kate 60 Kate 60
Problem Overview
Problem 01 Find Salary of an Employee: You will need to find the salary of “Ross”
from the dataset.
Problem 02 Find Salary and Department of an Employee: Your task is to return the
salary and the department separated with a comma.
Problem 03 VLOOKUP with Partial Match: You will find the salary of the employee
starts with “Ro”.
Problem 04 Return Value from a Range: The periodic payment (PMT) values are
given for a range of values. Your task is to calculate the PMT value for a range.
Problem 05 Group Ages in Range: The age is divided into five groups. Your task is to
sort the ages into the groups.
Problem 06 Return Second Match: In this problem, a name has repetitions. Your goal
is to find the value corresponding to the second occurrence of that name. Moreover,
use a helper column to count the instances of the salesman.
Problem 07 Nested VLOOKUP: Use the nested VLOOKUP to return values from two
related tables. The second column from the first table is the first column in the
second table. Use this to find the price of the A101 ID.
Problem 08 Find Duplicates: There are two columns containing the state names. Find
the duplicate state names using the VLOOKUP function.
Problem 09 Conditional Formatting with VLOOKUP: There are two columns that
contain marks from an exam. Students who got low marks can opt for a retake
examination. Highlight the students that took a retake exam and increased marks.
“Ross”
eturn the
mployee
es are
ge.
task is to
s. Your goal
Moreover,
rom two
the
ames. Find
ns that
take
marks.
