ASSIGNMENT – 33

Use of Formulas – VLOOKUP & IF

Topic: Employee Salary, Incentive, Bonus & TA Analysis

In this assignment, you will use VLOOKUP to fetch Incentive, Bonus and TA from separate tables and IF to calculate Salary Grade.

PART A – Employee Salary Data

Enter the following data in Excel:

EMP ID EMP NAME DEPARTMENT REGION SALARY INCENTIVE BONUS TA
201 Aarav Sales North 18,500      
202 Priya Sales East 13,200      
203 Rohan Marketing North 9,800      
204 Neha IT West 16,500      
205 Karan IT North 11,750      
206 Simran Finance East 14,300      
207 Rahul Sales North 10,500      
208 Pooja Marketing South 12,800      
209 Mohit Finance West 17,200      
210 Ananya HR North 15,600      
211 Vikas HR East 8,900      
212 Divya Admin South 11,200      

PART B – Department Incentive & Bonus Table

DEPARTMENT INCENTIVE BONUS
Sales 150 900
Marketing 250 800
IT 350 700
Finance 450 600
HR 550 500
Admin 650 400

PART C – Region TA Table

REGION TA
North 100
East 200
West 300
South 400

PART D – VLOOKUP

Q.1 Using VLOOKUP, calculate the Incentive for all employees according to their Department.

Q.2 Using VLOOKUP, calculate the Bonus for all employees according to their Department.

Q.3 Using VLOOKUP, calculate the TA for all employees according to their Region.

Q.4 Find the Incentive, Bonus and TA of Neha using VLOOKUP.

Q.5 Find the Incentive, Bonus and TA of Mohit using VLOOKUP.

PART E – IF Function

Q.6 Create a new column named SALARY GRADE.

Apply the following conditions:

Salary Condition Grade
Salary > 15,000 A
Salary > 10,000 B
Otherwise C

Use IF to calculate the Salary Grade.

Formula

If Salary is in E2:

=IF(E2>15000,"A",IF(E2>10000,"B","C"))

Copy the formula down for all employees.

Q.7 Find the Salary Grade of:

  1. Aarav

  2. Rohan

  3. Divya

using the IF function.

PART F – Combined Practice

Q.8 Find how many employees belong to the Sales and Marketing departments.

Use:

=COUNTIF(C2:C13,"Sales")+COUNTIF(C2:C13,"Marketing")

Q.9 Find how many employees belong to the North Region.

Use:

=COUNTIF(D2:D13,"North")

Q.10 — Total Pay

Create a new column named TOTAL PAY.

Calculate the total pay of every employee using:

Salary + Incentive + Bonus + TA

If:

  • Salary = E2

  • Incentive = F2

  • Bonus = G2

  • TA = H2

Use:

=E2+F2+G2+H2

Copy the formula down for all employees.

VLOOKUP Formula Examples

Incentive

If the Department Incentive table is in J2:L7:

=VLOOKUP(C2,$J$2:$L$7,2,FALSE)

Bonus

=VLOOKUP(C2,$J$2:$L$7,3,FALSE)

TA

If the Region TA table is in N2:O5:

=VLOOKUP(D2,$N$2:$O$5,2,FALSE)

Important Learning Point

Here, Department is the common lookup value for:

Department → Incentive + Bonus

And Region is the lookup value for:

Region → TA

So the employee’s final Total Pay can be calculated automatically after using VLOOKUP.

Skills Covered

Students will practice:

  • VLOOKUP

  • Multiple lookup tables

  • Exact Match using FALSE

  • Absolute references $

  • IF function

  • COUNTIF

  • Combining VLOOKUP with arithmetic formulas

  • Total salary/pay calculation

  • Practical employee payroll analysis

PreviousNext