Presentation of function VLOOKUP approximate
Unlike using VLOOKUP with the optional = 0 to return a true value, here we will get a value between 2 slices of values.
In the following document, you show the percentage of commissions paid by volume of sales.
As there is little chance the sales volume achieved by the commercial falls exactly on one of values in table, set up a search on exact value (optional parameter to 0) makes no sense.
Here, we want to return a value between 2 slices. So, in this case, we will use function VLOOKUP by approaching value.
In case of a search value approaching, Excel will recover value which corresponds to desired value even if that be greater.
For example, we want to know percentage of commission payable to a seller when it sold 15 vehicles. In our table, we have no data for 15. By cons, we know between 10 and 19 cars sold, commercial has a 5% premium.
So we will use VLOOKUP value approaching to get discount percentage between 10 and 19. Formula for cell C2 will be equal to
As you can see, although value 15 is not present in our reference table, VLOOKUP value approaching, we returned information on the value closest to 15 (or the data related to the value 10).
You have to understand that searching in value approaching, Excel retrieves the last value read. For example, if we had value 19, it is still linked to the data value 10 that would appear in cell C3.
As against 20 for value in B4, we have reached a new portion of our reference table, then we return information related to this new value. Complete Excel document below to view commissions payable to each business.
Warning: It is essential that your reference table shows values ?? sorted in ascending order for function you just returns a result.
If data in your reference table is not sorted, you will have result, false, as follows:
You can use VLOOKUP (option true) instead of using 4 IF functions. We will take the example of students’ grades and graded as using VLOOKUP.
In this example, instead of writing four overlap IF, you just write following formula to the same result.