Categorize data in excel using VLOOKUP exercise

Free Preview

A major benefit of the approximate match option is that you can use it to categorise information. So if you have the ages of all employees but want to see them in 3 groupings (<20 years old, between 20 and 40, and >40 years old for example) you can use this feature. In fact it is the better option than using nested IF functions.

In the file you downloaded look for the sheet name ‘VLOOKUP-Catergorise-1’

In this example we want to take the employees individual ratings and categorize them into one of 5 bands.

  • In D11 to D30, create a formula that will lookup the rating in column C to match the appropriate description from the table in K11 to L15 so for example a rating of 3.6 should bring back an ‘Inconsistent’ description.

Video Instructions

To follow along go to 5_1_1


Before you watch the solution, if your answers seem incorrect consider the typical errors made:

  • You $ signs are wrong. When you copied the formula down the dollar signs are often a problem. If you struggle with $ signs make sure you do the Excel $ signs lessons.

Video Solution

To follow along go to 5_1_2

Back to: Free VLOOKUP Course > VLOOKUP Approximate Match