ASSIGNMENT – 34

ANSWER KEY – VLOOKUP WITH TRUE / FALSE

Salary Slab Reference

Salary Range Incentive Grade
1 – 5,000 5% A
5,001 – 10,000 10% B
10,001 – 15,000 15% C
15,001 – 20,000 20% D
20,001 – 25,000 25% E
25,001 – 30,000 30% F
30,001 – 35,000 35% G
35,001 and above 40% H

Q1. Incentive for All Employees

Formula:

=VLOOKUP(D2,$H$2:$J$9,2,TRUE)
Emp Code Employee Salary Incentive
101 Aarav 8,500 10%
102 Priya 12,500 15%
103 Rohan 15,800 20%
104 Neha 19,500 20%
105 Karan 22,000 25%
106 Simran 27,500 30%
107 Rahul 31,500 35%
108 Pooja 35,500 40%
109 Mohit 14,000 15%
110 Ananya 24,500 25%
111 Vikas 7,500 10%
112 Divya 29,000 30%
113 Arjun 18,000 20%
114 Riya 33,500 35%
115 Nitin 5,000 5%

Q2. Grade for All Employees

Formula:

=VLOOKUP(D2,$H$2:$J$9,3,TRUE)
Emp Code Employee Salary Grade
101 Aarav 8,500 B
102 Priya 12,500 C
103 Rohan 15,800 D
104 Neha 19,500 D
105 Karan 22,000 E
106 Simran 27,500 F
107 Rahul 31,500 G
108 Pooja 35,500 H
109 Mohit 14,000 C
110 Ananya 24,500 E
111 Vikas 7,500 B
112 Divya 29,000 F
113 Arjun 18,000 D
114 Riya 33,500 G
115 Nitin 5,000 A

Q3. Incentive and Grade of Aarav

Aarav’s Salary = ₹8,500

According to the salary slab:

  • Incentive = 10%

  • Grade = B

Answer:

Aarav → 10% Incentive, Grade B

Q4. Incentive and Grade of Riya

Riya’s Salary = ₹33,500

According to the salary slab:

  • Incentive = 35%

  • Grade = G

Answer:

Riya → 35% Incentive, Grade G

Q5. Incentive and Grade of Pooja

Pooja’s Salary = ₹35,500

According to the salary slab:

  • Incentive = 40%

  • Grade = H

Answer:

Pooja → 40% Incentive, Grade H

Q6. Incentive and Grade of Rahul

Rahul’s Salary = ₹31,500

According to the salary slab:

  • Incentive = 35%

  • Grade = G

Answer:

Rahul → 35% Incentive, Grade G

Q7. VLOOKUP with FALSE – Employee Names

Given Emp Codes:

Emp Code Employee Name
103 Rohan
108 Pooja
111 Vikas
114 Riya

Formula:

=VLOOKUP(A2,$A$2:$B$16,2,FALSE)

Answers:

  • 103 → Rohan

  • 108 → Pooja

  • 111 → Vikas

  • 114 → Riya

Q8. Department of Emp Code 105 and 112

Emp Code 105

Karan → Marketing

Emp Code 112

Divya → IT

Formula:

=VLOOKUP(A2,$A$2:$C$16,3,FALSE)

Answer:

Emp Code Department
105 Marketing
112 IT

Q9. Salary of Emp Code 107

Emp Code 107 = Rahul

Rahul’s Salary = ₹31,500

Formula:

=VLOOKUP(A2,$A$2:$D$16,4,FALSE)

Answer:

₹31,500

Q10. Difference Between TRUE and FALSE

VLOOKUP with TRUE VLOOKUP with FALSE
Approximate Match Exact Match
Finds the closest matching value Finds the exact value
Lookup table should be sorted in ascending order Sorting is not required
Useful for slabs/ranges Useful for IDs/Codes
Example: Salary → Incentive Example: Emp Code → Employee Name

Example – TRUE

=VLOOKUP(19500,H2:J9,2,TRUE)

Result = 20%

Because ₹19,500 falls in the ₹15,001–₹20,000 slab.

Example – FALSE

=VLOOKUP(107,A2:D16,2,FALSE)

Result = Rahul

Because Emp Code 107 must be matched exactly.

Final Employee Summary

Emp Code Employee Salary Incentive Grade
101 Aarav 8,500 10% B
102 Priya 12,500 15% C
103 Rohan 15,800 20% D
104 Neha 19,500 20% D
105 Karan 22,000 25% E
106 Simran 27,500 30% F
107 Rahul 31,500 35% G
108 Pooja 35,500 40% H
109 Mohit 14,000 15% C
110 Ananya 24,500 25% E
111 Vikas 7,500 10% B
112 Divya 29,000 30% F
113 Arjun 18,000 20% D
114 Riya 33,500 35% G
115 Nitin 5,000 5% A
PreviousNext