Google Answers Logo
View Question
Q: Excel - Calculating totals using a lookup table ( Answered 5 out of 5 stars,   4 Comments )
Subject: Excel - Calculating totals using a lookup table
Category: Computers > Software
Asked by: jb23-ga
List Price: $20.00
Posted: 13 Jan 2005 07:56 PST
Expires: 12 Feb 2005 07:56 PST
Question ID: 456608
I am trying to create a spreadsheet for commission structure which
adds amounts based on different percentages found in a lookup table. 
Find the range for the sales number given and subtract the number
found in the lookup from sales number, then add all lower number
calculations in the range together to get the commission. Example:
$12,890 in sales.  The table has 4 levels of percentages in the
lookup. From $14000-15000; 18%. $12000-14000; 15%. $10000-12000; 10%. 
$8500-$10000; 5%.  In this case the lookup would find $12000 in the
table.  Commision would be $890*15% + $2000*10% + $1500*5% or $408.50.

Request for Question Clarification by maniac-ga on 13 Jan 2005 18:37 PST
Hello Jb23,

If you are satisfied with the comment made by Jack_Of_Few_Trades, I
suggest you close the question. If you want a more complete answer,
please indicate in a question clarification.

For example, I could provide an example of using the table look up
(instead of hard coding the values in the formula) from the


Clarification of Question by jb23-ga on 14 Jan 2005 05:19 PST
I started out with a lookup table.  If you have a solution using a
table, please provide that as an answer.  It would be easier to
Subject: Re: Excel - Calculating totals using a lookup table
Answered By: joey-ga on 15 Jan 2005 00:37 PST
Rated:5 out of 5 stars
The best way to do this is to first add an extra column to the lookup
table to sum up the total comission for all of the rate brackets below
each level

Here is how the lookup table should be set up:

   A                 B       C
1  Threshold Floor   Rate    Base Comission
2  0                 0%      0
3  8500              5%      0
4  10000             10%     75
5  12000             15%     275
6  14000             18%     575
7  15000             0%      755

The "threshold floors" and "rates" would be manually placed in the
lookup table by you.  The "base commission" cells, however, would be
filled by formula.

The formula in C3 would be:

You should enter that in C3 and then highlight from C3 to C7 and then
press CTRL-D (or go Edit: Fill: Down) to copy the formula down into
all the column-C cells.

Call your lookup table spreadsheet "rates" (you can do this by
double-clicking that spreadsheet's tab at the bottom of the screen and
renaming it)

Then, on your main spreadsheet, let's assume it looks something like this:

   A         B
1  Sales     Commission
2  12890.00  408.50
3  16000.00  755.00


The values in A would be placed by you.  B would be filled by lookup
formula.  In B2, place the following formula:

This is what it does:
    1. Using the sales number in A2, it . . .
    2. Finds the correct percentage to use, then
    3. Multiplies it by the threshold bottom subtracted from the sales, then
    4. Adds in the total commission for the thresholds below.

With this example, here is what the formula pieces provide for B2:


Please let me know if you have any questions.


     Personal knowledge of Excel

For more information on VLOOKUP, search Google for:
     vlookup excel
jb23-ga rated this answer:5 out of 5 stars
Thanks! This is what I was looking for.

Subject: Re: Excel - Calculating totals using a lookup table
From: jack_of_few_trades-ga on 13 Jan 2005 12:05 PST
Here's a way to do it:

Put this formula in any cell other than A1:

Then input the sales into A1.

**this will not work for values over $15,000 as that's not specified
in the questions, but you can add another IF statement for that if
you'd like.
Subject: Re: Excel - Calculating totals using a lookup table
From: jb23-ga on 13 Jan 2005 13:23 PST
This works.  Thanks!
Subject: Re: Excel - Calculating totals using a lookup table
From: shockandawe-ga on 13 Jan 2005 17:14 PST
Better (prettier & more flexible) to use the vlookup function
Subject: Re: Excel - Calculating totals using a lookup table
From: jack_of_few_trades-ga on 14 Jan 2005 06:17 PST
Don't stray from the ways of hard coding!  You know you love it!!!

I mean...  flexible tables is a far better way to go unless the
numbers are forever set in stone.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy