↑ Return to Find

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)

14 comments

Skip to comment form

  1. lel

    fuck her right in de !!!!!!

  2. Anonymous

    hi

  3. robert

    feeethers payssion fruit goorls tres reeeeeding

  4. Anonymous

    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

  9. steve

    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

Leave a Reply

%d bloggers like this: