Look up - i think

  • Thread starter Thread starter Alan Davies
  • Start date Start date
A

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
 
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.
 
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)
 
Back
Top