ASSIGNMENT – 34

USE OF VLOOKUP WITH TRUE / FALSE

Objective

In this assignment, you will learn how to use VLOOKUP with TRUE (Approximate Match) and VLOOKUP with FALSE (Exact Match).

Part A – Employee Salary Data

Enter the following data in Excel.

Emp Code Employee Name Department Salary Incentive Grade
101 Aarav Sales 8,500    
102 Priya Finance 12,500    
103 Rohan IT 15,800    
104 Neha HR 19,500    
105 Karan Marketing 22,000    
106 Simran Sales 27,500    
107 Rahul IT 31,500    
108 Pooja Finance 35,500    
109 Mohit HR 14,000    
110 Ananya Sales 24,500    
111 Vikas Marketing 7,500    
112 Divya IT 29,000    
113 Arjun Finance 18,000    
114 Riya HR 33,500    
115 Nitin Sales 5,000    

Part B – Salary Slab Table

Important

The Salary Slab Table must be arranged in ascending order because we are using TRUE / Approximate Match.

Salary Slab Incentive Grade
1 5% A
5,001 10% B
10,001 15% C
15,001 20% D
20,001 25% E
25,001 30% F
30,001 35% G
35,001 40% H

VLOOKUP with TRUE

Incentive Formula:

=VLOOKUP(D2,$H$2:$J$9,2,TRUE)

Grade Formula:

=VLOOKUP(D2,$H$2:$J$9,3,TRUE)

Adjust the table range according to where you place the Salary Slab Table in your worksheet.

Remember:

TRUE = Approximate Match

Excel finds the largest slab value that is less than or equal to the salary.

For example:

Salary = 19,500

The matching slab is 15,001, so:

  • Incentive = 20%

  • Grade = D

Part C – Questions: VLOOKUP with TRUE

Q1.

Using VLOOKUP with TRUE, calculate the Incentive for all employees according to their Salary.

Q2.

Using VLOOKUP with TRUE, calculate the Grade for all employees according to their Salary.

Q3.

Find the Incentive and Grade of Aarav.

Q4.

Find the Incentive and Grade of Riya.

Q5.

Find the Incentive and Grade of Pooja.

Q6.

Find the Incentive and Grade of Rahul.

Part D – VLOOKUP with FALSE

Q7. Create the following Exact Match Table

Emp Code Employee Name
103  
108  
111  
114  

Use VLOOKUP with FALSE to find the Employee Name according to the Emp Code.

Formula:

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

FALSE = Exact Match

Q8.

Find the Department of the following employees using VLOOKUP with FALSE:

  • Emp Code 105

  • Emp Code 112

Q9.

Find the Salary of Emp Code 107 using VLOOKUP with FALSE.

Part E – Conceptual / Advanced

Q10.

Explain the difference between:

=VLOOKUP(...,TRUE)

and

=VLOOKUP(...,FALSE)

Also write one practical example of each.

Hint:

TRUE – Approximate Match

  • Used with slabs, ranges or categories.

  • Lookup table must be sorted in ascending order.

  • Example: Salary → Incentive/Grade.

FALSE – Exact Match

  • Used when you need an exact value.

  • Lookup column does not need to be sorted.

  • Example: Employee Code → Employee Name.

Learning Outcome

After completing this assignment, students should be able to:

  1. Understand Exact Match vs Approximate Match.

  2. Use VLOOKUP with TRUE.

  3. Use VLOOKUP with FALSE.

  4. Create and use a Salary Slab Table.

  5. Understand why approximate-match tables must be sorted.

  6. Apply VLOOKUP to real-world salary and employee data.

PreviousNext