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.
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.
To follow along go to 5_1_2