Excel Formula

S

SAP

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.
 
G

Gary''s Student

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top