Look up - i think

[Alan Davies]

i am using a workbook to calculate commission payments
one worksheet contains info on the sale made:
Name of customer/date/product/rental/term of rental/total value etc
The product is a drop down from a list in another sheet
Also on the other sheet is the commission I rate for calcualting the value
of the sale
What i want to do is write a formula that allows for the sale data to be
entered, then for the commission to be calcluated based on the results of
selelcting the drop down.

For example:
Column d = product from drop down
Column g - total order value
column i - Total order value *(the rate of commission of the product
selected at Column d - the value is in the same worksheet as teh product
list - one column to the right of the product name)

Can anyone help me

TIA

 

[Guest]

If your product list is sorted, you can use VLOOKUP to find the commission
rate, eg in row 2
=VLOOKUP(D2,Products,2,FALSE)*I2
If your products list range name does not include the rate column, then
select the product range as well as the rate cells, click on
Insert|Name|Define, and give it a descriptive name, eg Products as in the
sample, or Prodcomm.

 

[Guest]

Sorry, to keep it neat, and not display anything before entries have been
made, use the following instead:
=IF(I2="","",VLOOKUP(D2,Products,2,FALSE)*I2)