Finding items with a certain quantity within price breaks

[Benedikt]

Dear chandoo-Team,
I have joint recently your excel family and am very thrilled about what I learned with you already. Thanks a lot for all that good stuff.
For some time I have been struggling with a certain issue during my work and therefore was wondering if chandoo could help in this case.
Currently I am searching for a formula which gives me a certain price in a price list even if we have unregular price breaks for different items.
Example:
articlequantitiesprice breakOrderordered articleeffective price
0001121,209 0001?
0001241,6012 0002?
0001361,8025 0003?
0001482,1030 0001?
00011003,2047 0003?
00012004,40174 0002?
000220012,00264 0001?
000225015,00444 0001?
000230020,00234 0003?
000335026,00371 0002?
000345031,00452 0002?
Now I would need a formula, which gives me the price within the respective price breaks for the given item in the very right column. Vlookup is unfortunately insufficient and index formula doesn't work for me. Maybe it does but currently I don't know how to apply it for price breaks.

If you have a idea how to approach this problem, I would be very happy if you could share that.

 

[NARAYANK991]

Hi ,

Please upload your workbook with this data in it.

Specify the cell(s) where you want the formula entered.

Narayan

 

[Benedikt]

hey there, I just uploaded a quick example of my described. Problem.

Lets assume you have a table as basis where you have all articles there are in your portfolio and the given price breaks. Now you have the order at the other side with the article-nr und the quantity. How to find out the right price respecting the certain price breaks for the items, which can differ from article to article.

Maybe we can solve that together.
Thanks in advance

 

[NARAYANK991]

Hi ,

Can you fill up a few cells manually ?

I am not able to understand how the cells have to be populated.

Narayan

 

[Benedikt]

sorry for the delay. Please find attached the updated workbook. Hope that clarifies a little bit more the problem. As explanation you can consider columns A,B,C as kind of a basis for all information in columns D and E. What is in D and E should be extracted into column F.
The effective price should represent the price within the given price breaks in column A,B,C. I have inserted some of first values in column F to show the required data. I would like to automize with a formula, because I often have orders with a certain amount of a specific article but no specific prices. The prices I need to extract normally are in a different price catalog only with price breaks. So therefore I need to create a formula, which reads the article and the quantity and extracts the correct price out of the catalog.

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan