Search Not Just Numbers

Loading...

Tuesday, 16 October 2012

EXCEL TIP: Look up commission or tax rates from earning bands

Just a short post today on a very useful use for the VLOOKUP function.

Typically when we use VLOOKUP, we are looking for an exact match from a list. For example, we might look up a customer's address, based upon their account code.

But what if we want to look up the correct commission rate or tax rate, based upon what earnings band a particular value falls into?

VLOOKUP handles this extremely well, when you understand its mysterious fourth (optional) argument.

The syntax of a VLOOKUP function is:

=VLOOKUP(lookup value,range to lookup,column to return,TRUE or FALSE)

I you need a recap on using the VLOOKUP function, take a look at my earlier post on the subject.

First of all though, let us outline the problem with an example.

We have a cell (D2) containing an income figure upon which we need to calculate a sales commission.

However, the rate of commission changes depending on the size of the income figure as follows:

0 - 9,999  pays 10%
10,000 - 49,999 pays 20%
and 50,000+ pays 25%

How do we calculate what rate to use?

We could use IF statements, however this falls down with more bands and is very inflexible when it comes to changing the bands. A far better solution is to use the VLOOKUP function.

We need to create a two column table with the start of the band in the first column, and the rate to use in the second. This table must be in ascending order of earnings, i.e.



When we set the final argument in the VLOOKUP to TRUE (or leave it blank), it scans the lookup table for the highest earnings value that is still less than the lookup value, so:

=VLOOKUP(D2,A2:B4,2,TRUE)

will return the rate we are looking for.

We can also use exactly the same approach for other similar problems, such as looking up tax rates.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

1 comment:

  1. As a lot of girls acquire never even captivated a job or acceptable money of their own, it would be antic for them to louis vuitton replica even anticipate of spending the sums of money appropriate to acquirement artist characterization handbags. That array of money should alone be spent by anyone who has acceptable their own money and knows that they are able to allow such a abundant acquirement on their own. The botheration that abounding girls face, however, is associate pressure. At school, there will accordingly be some girls accretion the replica louis vuitton latest artist characterization handbags, bought with their parents' hard-earned money. And in boyish society, the burden to fit in with the acclimatized army is enormous. This usually agency bathrobe the aforementioned way the blow of the acclimatized citizenry dresses, in adjustment to fit in. It would hardly be reasonable for any babe not from an badly affluent ancestors to rolex replica uk ask her parents to absorb such abounding amounts of money on something like a handbag. This is area replica backpack broad artist handbags appear in. These are broad handbags that acquire been advised according to the a lot of acclimatized artist characterization handbags, and yet do not bulk about as much, as they do not backpack any anatomy of artist label. In agreement of looks and style, however, you would be hard-pressed to acquaint the aberration amid broad handbags modeled afterwards artist handbags and the artist handbags themselves. This is the affectionate of accommodation on artist actualization accessories that abounding girls appear to acquire and even treasure. This is because broad accessories such as broad handbags allows even girls from lower assets families to gucci replica dress like their admired celebrities, aimless about boondocks with a artist backpack on their arm. No one needs to apperceive that their backpack which looks so abundant like a artist characterization backpack is in fact one of the replica backpack broad artist handbags that are calmly accessible online. The differences are about ephemeral unless anyone were to appraise the characterization on the rolex replica central of the handbag.

    ReplyDelete