Vlookup with Approximate Match, Range True, Range 1

Normally we know that Vlookup matches value-to-value directly.

For example, if you want to know the age of an employee, when you enter the employee’s code, you can vlookup his/her age directly.

But now against each employee’s age, you want to enter an Age Slab based on the age in years,

0 to 20

21 to 25

26 to 30

31 to 35

36 to 40

More than 40

 

This can be done easily using Vlookup with – Approximate Match / Range = True / Range =1

Your data looks like this,

Steps to get the respective Age slabs

• Create the below structure outside the data somewhere
  • Note, the age must be in the ascending order only
    

    Age slab data structure

• Use this Vlookup formula, =VLOOKUP(C2,$G$2:$H$8,2,1)
  

  Vlookup formula

  • C2 = We are looking for the Age of each employee.
  • G2:H8 = We are looking for that Age in this data where the slabs are mentioned.
  • 2 = We want the second column as the output, that is, the slab.
  • 1 = With 1 at the end instead of 0, we are forcing Vlookup to look for a Range instead of Direct match.
• Thus,
  • All values between 0 and 20, will get the Slab “Less than 20”
  • All values >20 to 25, will get the Slab “20 to 25”
  • All values >25 to 30, will get the Slab “25 to 30”
  • All values >30 to 35, will get the Slab “30 to 35”
  • All values >35 to 40, will get the Slab “35 to 40”
  • All values >40, will get the Slab “More than 40”
    

    How it works