Excel 2007 Problem: Formula to return the correct price for the quantity given?

[caplinq]

Assume Product is Part A and Qty is 4 for the following fields. What is the formula I need to enter to return the correct price for the given quantity?
(I expect the formula to output 25.87)
Field values are as follows:
A1:Product B1:Price C1:Qty Start D1: Qty End
A2:PartA B2:10.35 C2:50 D2:500
A3:PartA B3:12.94 C3:25 D3:49
A4:PartA B4:12.94 C4:10 D4:24
A5:PartA B5:25.87 C5:1 D5:9
A6:PartA B6:10.35 C6:0 D6:0
A7:PartB B7:8.34 C7:50 D7:500
A8:PartB B8:10.43 C8:25 D8:49
A9:PartB B9:10.43 C9:10 D9:24
A10:PartB B10:20.86 C10:1 D10:9
A11:PartB B11:8.34 C11:0 D11:0

Additional Details
I tried a previous solution which was similar, except Quantity was 10 (which is in the Qty Start List). This question will need the answerer to allow for the fact that the quantity is BETWEEN QTY START and QTY END columns (in this case 1 and 9) and not exactly any number in the Qty start or Qty End list)

The previous correct solution was as follows:
=SUMPRODUCT((A2:A11=A2) * (C2:C11=1) * B2:B11)

 

[Peter_SSs]

Welcome to the MrExcel board!

My suggestion would be to sort the data by Product, then by 'Qty Start' (ascending) then use the formulas shown. Could be done in one formula but using the extra helper cell simplifies the G4 formula a bit.

Excel Workbook
	A	B	C	D	E	F	G
1	Product	Price	Qty Start	Qty End		Product	PartB
2	PartA	10.35	0	0		Qty	4
3	PartA	25.87	1	9		Start Row	7
4	PartA	12.94	10	24		Price	20.86
5	PartA	12.94	25	49
6	PartA	10.35	50	500
7	PartB	8.34	0	0
8	PartB	20.86	1	9
9	PartB	10.43	10	24
10	PartB	10.43	25	49
11	PartB	8.34	50	500
12
Lookup

 

[caplinq]

Hi Peter - Oh, what a great start! I have spent hours and hours trying to get something working and this is by far the closest I've come.

Your "helper cell" helps me to understand what is going on too.

I have a couple issues with this formula:
1 - The data that I need to access is given to me "as is", so yes I could sort it as you suggest, but it would be a manual process that I might forget sometimes. Is it possible to do this without sorting first?
2- The data I get may be very long, but how long I can never be sure. In your formula, G4 you use :B20 which assumes the column length is a maximum of 20 rows. Is there a way to improve this formula to "take the last row of data"?
3 - My data actually has a column "Currency" between B & C which I can ignore for my formula, but does the formula need to be adjusted if there is an extra column there?

caplinq

 

[Peter_SSs]

1 - The data that I need to access is given to me "as is", so yes I could sort it as you suggest, but it would be a manual process that I might forget sometimes. Is it possible to do this without sorting first?

No sensible way using standard formulas comes to mind. It could be done with a macro.

2- The data I get may be very long, but how long I can never be sure. In your formula, G4 you use :B20 which assumes the column length is a maximum of 20 rows. Is there a way to improve this formula to "take the last row of data"?

Just change the 20 to something that is bigger than your data is ever likely to be.

3 - My data actually has a column "Currency" between B & C which I can ignore for my formula, but does the formula need to be adjusted if there is an extra column there?

This shouldn't be a problem just make sure the formulas are pointing at the 'Product', 'Price' and 'Qty Start' columns.

 

[caplinq]

No sensible way using standard formulas comes to mind. It could be done with a macro.

Ok, I can deal with this.

Just change the 20 to something that is bigger than your data is ever likely to be.

I tried that, but then the value returned didn't give the right answers anymore. The first one cell G3 works fine, it's the G4 that I am having issues with.

From your equation:
<table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr><tr><td>G4</td><td>=INDEX(INDEX(B1:B20,G3):B20,MATCH(G2,INDEX(C1:C20,G3):C20,1))</td></tr></tr></tbody></table>I tried this:
<table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr><tr><td>G4</td><td>=INDEX(INDEX(B1:B800,G3):B800,MATCH(G2,INDEX(C1:C800,G3):C800,1))</td></tr></tr></tbody></table>but it no longer returns the right number.

I tried troubleshooting it, and it seems it's the last C800 that is giving me the wrong number, but I can't figure out what it should be.

This shouldn't be a problem just make sure the formulas are pointing at the 'Product', 'Price' and 'Qty Start' columns.

Ok, no problem.

Thanks for your help so far,
caplinq

 

[Peter_SSs]

I tried that, but then the value returned didn't give the right answers anymore. The first one cell G3 works fine, it's the G4 that I am having issues with.

My mistake, it comes back to having to have the data sorted ascending. I need to restrict the number of rows looked at by the formula. Try this

Excel Workbook
	A	B	C	D	E	F	G
1	Product	Price	Qty Start	Qty End		Product	PartB
2	PartA	10.35	0	0		Qty	17
3	PartA	25.87	1	9		Start Row	7
4	PartA	12.94	10	24		End Row	11
5	PartA	12.94	25	49		Price	10.43
6	PartA	10.35	50	500
7	PartB	8.34	0	0
8	PartB	20.86	1	9
9	PartB	10.43	10	24
10	PartB	10.43	25	49
11	PartB	8.34	50	500
12
Lookup

 

[caplinq]

My Life! I've got it back! Thank you so much for this... I can sleep tonight and finish this finally. Thank you, thank you.

caplinq