Excel 2007 formula to find price based on 2 fields

Posted on 2012-08-14
Last Modified: 2012-08-15
Hello EE,

I have the following
Material Code              QTY700          QTY2000         QTY4000           QTY8000
32                                       .40                  .33                  .20                     .19
What I am looking for is if there is a formula that would say if the Material code is x and the quantity y then the price will be z.  Thus if material is 32 and quantity is 1500 which is over 700 but under 2000 the price would be .40
Question by:bergquistcompany
    LVL 50

    Expert Comment

    by:barry houdini
    It's simpler if the headers are just values, 700, 2000 etc.

    If they are in B1:E1 and the Material codes are in A2:A10 then try this formula


    where A12 = 32 and B12 = 1500

    see this article for a better description

    regards, barry
    LVL 44

    Expert Comment

    by:Martin Liss
    Where is quantity found?
    LVL 4

    Expert Comment


    Open in new window

    Threw this together quickly but I believe it should do what you're asking.  It does only returns the unit price but could, of course, be easily modified to return a total line price.  It also doesn't return a value for quantities less than 700.
    You may wish to convert your column headers into just the numeric quantities which would allow you to reference them should the quantity required for price break points need to be changed.

    Leaving answer here for reference however the index answer above is certainly a better way to go about it.  I hadn't realized that the match function would work properly given the ranges.  I love learning new tricks!

    Author Comment

    @barryhoudini I get #N/A and also if I put the example into Excel and try it I get the same error so I'm wondering if there is something I'm missing

    Quantity is found in B1 is 700 C1 is 2000

    @Dustin I am not getting a value on this.  Am I supposed to enter a value for the A??
    LVL 9

    Expert Comment

    What values do you have for cells A2, A3, etc?

    Do you have ther the material codes 32, 21, etc?
    LVL 50

    Accepted Solution

    I attached an example with some added random values...

    I used the same formula I suggested above except I added some $ signs to the ranges so that they don't change when the formula is copied down a column, so in the attached example the formula in C12 is this


    ....copied down to C14

    That gives you the unit price for the material and quantity shown. In the next column I just multiplied the unit price by the quantity to get the Total Price. Of course you can do that all in one formula if you want like this:


    As Dustin says, if quantity is less than 700 you'll get an error, is that possible? If so you'll need to enter another column, show zero in the header and below that the prices for 0-699

    regards, barry

    Author Closing Comment

    Perfect just what I needed thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now