# VLOOKUP (Approximate)

This document will show you in which case you will use VLOOKUP function with the optional parameter = 1 (approaching). This sheet does not include explanations of construction of the function we have seen in “VLOOKUP (Exact Match)“.

## 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.

Take for example the case of compensation for a commercial company.

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.

## Explanation

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

=VLOOKUP(B2,\$E\$4:\$F\$8,2,1)

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:

## Exercise

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.

=VLOOKUP(E2,\$E\$11:\$F\$15,2,1)

1. ##### lel

fuck her right in de !!!!!!

hi

3. ##### robert

feeethers payssion fruit goorls tres reeeeeding

lol

5. ##### Keith Hodge

I’m making all kinds of gains…

All kiiiiiiiiiiiiiiiiiinds

6. ##### kyle is tiny

fuck you kyle

7. ##### Anonymous

steeeeeeeeeeve

8. ##### zyzz

get fucked cunt

mirin?

10. ##### Anonymous

Isn’t this the right formula for the exercise? =VLOOKUP(E2,\$H\$1:\$I\$5,2,1)

11. ##### Anonymous

very useful thank you

12. ##### Anonymous

It is very useful.

13. ##### ジャケット 販売

シャネル 財布 中古 ジャケット 販売 http://rexuechut.celinebyimmediatelyjp.org/

14. ##### Woomdog

However many years after you published this, it’s still coming in useful! Thank you