Excel Formula
 

I need to create a formula to work with a very large price list which carries
different part numbers with the respective description, List Price, weight,
and a Clasification Code according to the type of part.

My problem is that i quote the customer based on the different Clasification
codes; for intance,

P/N 123, with clasification code A, quote at List Price minus 10%
P/N 456, with clasification code B, quote at List Price minus 15%
P/N 789, with clasification code C, quote at List Price minus 20%

Hint: One part number has only one clasification code
One clasification code fit different part numbers (EX. for all the
parts for the Audi engines)

I created a VLookup formula that already brings me all the parts for the
quote but i need another column to show the selling prices. I tried the IF
function but it's limited to only a very few options.

Any help will be appreciated.

Thanks.
--
SAP1

Excel Formula
 

You need to do two lookups:

1. a VLOOKUP() to get the List Price given the part number
2. a two-dimensional lookup to get the discount given the part number and
classification

Once you have both the list price and the discount, you can calculate the
selling price.

Two-dimensional lookups are described in:

http://www.cpearson.com/excel/TablesAndLookups.aspx

checkout the first example.

--
Gary''s Student - gsnu200830


"SAP" wrote:

I need to create a formula to work with a very large price list which carries
different part numbers with the respective description, List Price, weight,
and a Clasification Code according to the type of part.

My problem is that i quote the customer based on the different Clasification
codes; for intance,

P/N 123, with clasification code A, quote at List Price minus 10%
P/N 456, with clasification code B, quote at List Price minus 15%
P/N 789, with clasification code C, quote at List Price minus 20%

Hint: One part number has only one clasification code
One clasification code fit different part numbers (EX. for all the
parts for the Audi engines)

I created a VLookup formula that already brings me all the parts for the
quote but i need another column to show the selling prices. I tried the IF
function but it's limited to only a very few options.

Any help will be appreciated.

Thanks.
--
SAP1