Assignment 33 – Answer Key
Use of Formulas – VLOOKUP & IF
Complete Answer Table
| EMP ID | EMP NAME | DEPARTMENT | REGION | SALARY | INCENTIVE | BONUS | TA | GRADE | TOTAL PAY |
|---|---|---|---|---|---|---|---|---|---|
| 201 | Aarav | Sales | North | 18,500 | 150 | 900 | 100 | A | 19,650 |
| 202 | Priya | Sales | East | 13,200 | 150 | 900 | 200 | B | 14,450 |
| 203 | Rohan | Marketing | North | 9,800 | 250 | 800 | 100 | C | 10,950 |
| 204 | Neha | IT | West | 16,500 | 350 | 700 | 300 | A | 17,850 |
| 205 | Karan | IT | North | 11,750 | 350 | 700 | 100 | B | 12,900 |
| 206 | Simran | Finance | East | 14,300 | 450 | 600 | 200 | B | 15,550 |
| 207 | Rahul | Sales | North | 10,500 | 150 | 900 | 100 | B | 11,650 |
| 208 | Pooja | Marketing | South | 12,800 | 250 | 800 | 400 | B | 14,250 |
| 209 | Mohit | Finance | West | 17,200 | 450 | 600 | 300 | A | 18,550 |
| 210 | Ananya | HR | North | 15,600 | 550 | 500 | 100 | A | 16,750 |
| 211 | Vikas | HR | East | 8,900 | 550 | 500 | 200 | C | 10,150 |
| 212 | Divya | Admin | South | 11,200 | 650 | 400 | 400 | B | 12,650 |
Q1. VLOOKUP Incentive for All Employees
Formula:
=VLOOKUP(C2,$J$2:$L$7,2,FALSE)
| Employee | Incentive |
|---|---|
| Aarav | 150 |
| Priya | 150 |
| Rohan | 250 |
| Neha | 350 |
| Karan | 350 |
| Simran | 450 |
| Rahul | 150 |
| Pooja | 250 |
| Mohit | 450 |
| Ananya | 550 |
| Vikas | 550 |
| Divya | 650 |
Q2. VLOOKUP Bonus for All Employees
Formula:
=VLOOKUP(C2,$J$2:$L$7,3,FALSE)
| Employee | Bonus |
|---|---|
| Aarav | 900 |
| Priya | 900 |
| Rohan | 800 |
| Neha | 700 |
| Karan | 700 |
| Simran | 600 |
| Rahul | 900 |
| Pooja | 800 |
| Mohit | 600 |
| Ananya | 500 |
| Vikas | 500 |
| Divya | 400 |
Q3. VLOOKUP TA for All Employees
Formula:
=VLOOKUP(D2,$N$2:$O$5,2,FALSE)
| Employee | Region | TA |
|---|---|---|
| Aarav | North | 100 |
| Priya | East | 200 |
| Rohan | North | 100 |
| Neha | West | 300 |
| Karan | North | 100 |
| Simran | East | 200 |
| Rahul | North | 100 |
| Pooja | South | 400 |
| Mohit | West | 300 |
| Ananya | North | 100 |
| Vikas | East | 200 |
| Divya | South | 400 |
Q4. Find Incentive, Bonus and TA of Neha
Neha → IT, West
-
Incentive = 350
-
Bonus = 700
-
TA = 300
Answer: 350, 700, 300
Q5. Find Incentive, Bonus and TA of Mohit
Mohit → Finance, West
-
Incentive = 450
-
Bonus = 600
-
TA = 300
Answer: 450, 600, 300
Q6. Calculate Salary Grade
Criteria:
-
Salary > 15,000 → A
-
Salary > 10,000 → B
-
Otherwise → C
Formula:
=IF(E2>15000,"A",IF(E2>10000,"B","C"))
Q7. Find the Salary Grade of Aarav, Rohan and Divya
| Employee | Salary | Grade |
|---|---|---|
| Aarav | 18,500 | A |
| Rohan | 9,800 | C |
| Divya | 11,200 | B |
Answers:
Aarav → A
Rohan → C
Divya → B
Q8. Count Employees in Sales and Marketing
Sales = 3
Aarav, Priya, Rahul
Marketing = 2
Rohan, Pooja
Total Sales + Marketing = 5
Formula:
=COUNTIF(C2:C13,"Sales")+COUNTIF(C2:C13,"Marketing")
Answer: 5
Q9. Count Employees in North Region
North employees:
-
Aarav
-
Rohan
-
Karan
-
Rahul
-
Ananya
Answer = 5
Formula:
=COUNTIF(D2:D13,"North")
Q10. Calculate Total Pay
Formula:
=E2+F2+G2+H2
or
=SUM(E2:H2)
| Employee | Total Pay |
|---|---|
| Aarav | 19,650 |
| Priya | 14,450 |
| Rohan | 10,950 |
| Neha | 17,850 |
| Karan | 12,900 |
| Simran | 15,550 |
| Rahul | 11,650 |
| Pooja | 14,250 |
| Mohit | 18,550 |
| Ananya | 16,750 |
| Vikas | 10,150 |
| Divya | 12,650 |
Final Important Answers
-
Q4 Neha: Incentive 350, Bonus 700, TA 300
-
Q5 Mohit: Incentive 450, Bonus 600, TA 300
-
Q7: Aarav A, Rohan C, Divya B
-
Q8: Sales + Marketing = 5 employees
-
Q9: North Region = 5 employees
-
Highest Total Pay: Aarav = 19,650
-
Lowest Total Pay: Vikas = 10,150