Previously we looked at using VLOOKUP to find an exact match i.e. it must find the value or give an error message. Now we will look at the VLOOKUP approximate match. An approximate match allows you to tell Excel to look ‘in-between’ the data to find an approximate match.
Why would you need this. In the video example you can see a perfect reason. We need to convert students test scores into symbols. Can we create a database that will allow for every possible score to give a symbol? What about if someone gets 68.235% ? This version of VLOOKUP allows you to search for the band into which this score will fit and extract the correct symbol. Other uses include:
- Interest charges when the rate differs depending on the bank balance
- Sales commission that ‘step up’ depending on sales achieved
- Determining a students symbol based on their test score
Most of the syntax is exactly the same. You still need to tell Excel what to look for, where it must look and what it must pull through.
The two main differences are:
- The last part of the syntax (range lookup) must be a TRUE.
- The database you are looking through MUST be sorted in ascending order.
Watch the video clip to see how this works
To follow along go to 4_0
To get a sense of how you can use this, perform the following exercises