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,

Data

Data

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

      Age slab data structure

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

    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

      How it works