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
- Use this Vlookup formula, =VLOOKUP(C2,$G$2:$H$8,2,1)
- 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”