Quote Creation Document - Requiring double lookup

[LyndseyK]

Hi
Would anyone be able to help me?
I am trying to create a document that will auto populate from an excel sheet with product and pricing, sheet 1 is the quote template, with sheet 2 being the data behind.

Sheet 1 - Quote template

	D	EFGH	I	J	K
12	Product	Desc	Qty	Unit Price	total
13	Code0001	Product x	2	2	£4

<tbody>
</tbody>

Sheet 2

A B C D E F G H I J K L M 2 Desc CODE 1 24.00 49.00 99.00 199.00 299.00 399.00 499.00 750.00 999.00 1000.00 3 Product x Code0001 £1 £2 £3 £4 £5 £6 £7 £8 £9 £10 £11 <tbody> </tbody>
I require cell J13 on Sheet 1, to look at cell D13 first, then correspond this to sheet 2 and the correct code and pricing. I have got the second part of the formula, which looks at the correct band, and results in that price. But what I need now is that same cell to look at the D13 to see what that corresponds to in sheet 2 to product the correct row pricing?

<tbody>
</tbody>

=IF(I13=Sheet2!$C$2,Sheet2!C3,IF(I13<=Sheet2!$D$2,Sheet2!D3,IF(I13<=Sheet2!$E$2,Sheet2!E3,IF(I13<=Sheet2!$F$2,Sheet2!F3,IF(I13<=Sheet2!$G$2,Sheet2!G3,IF(I13<=Sheet2!$H$2,Sheet2!H3,IF(I13<=Sheet2!$I$2,Sheet2!I3,IF(I13<=Sheet2!$J$2,Sheet2!J3,IF(I13<=Sheet2!$K$2,Sheet2!K3,IF(I13<=Sheet2!$L$2,Sheet2!L3,IF(I13<=Sheet2!$M$2,Sheet2!M3)))))))))))

Thank you

 

[Fluff]

Hi & welcome to MrExcel.
How about for J13
=INDEX(Sheet2!$C$3:$M$13,MATCH(D13,Sheet2!$B$3:$B$13,0),AGGREGATE(15,6,(COLUMN(Sheet2!$C$2:$M$2)-COLUMN(Sheet2!$C$2)+1)/(Sheet2!$C$2:$M$2>=$I13),1))

 

[LyndseyK]

Thank you for coming back so quick, however, doesn't seem to work as returns a #n/a.
D2 to M2 on sheet 2 is the pricing band, ie, if you order 50 units of "product x", the price is £4, if you order 49 units it is £3.

So do I need to insert this after each IF formula, or remove the entire formula and just use yours?

The formula basically needs to look at Sheet 1 D13, if that code is Product X on sheet 1 and corresponds to product x on sheet 2, then find that product on sheet 2 and look at the corresponding row. Then also look at quantity in sheet 1 I13, if that is less than or equal to row d2(for example and continuing across as per my formula) then return the value in row D3 that corresponds to that product code.
I hope this makes sense, I know it is a little complex.

This needs to run down through 100s of product codes and pricing bands but once I get it sorted for one, I will manage that.

 

[Fluff]

Replace the formula you have in J13 with the one I suggested.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
2	Desc	CODE	1	24	49	99	199	299	399	499	750	999	1000
3	Product x	Code0001	£1	£2	£3	£4	£5	£6	£7	£8	£9	£10	£11
4		Code0002	£10	£11	£12	£13	£14	£15	£16	£17	£18	£19	£20
Sheet2

Book1
	D	E	F	G	H	I	J	K
12	Product	Desc				Qty	Unit Price	total
13	Code0001	Product x				2	2	£4
14	Code0002					24	11	£264
Sheet1

Cell Formulas
Range	Formula
J13	=INDEX(Sheet2!$C$3:$M$13,MATCH(D13,Sheet2!$B$3:$B$13,0),AGGREGATE(15,6,(COLUMN(Sheet2!$C$2:$M$2)-COLUMN(Sheet2!$C$2)+1)/(Sheet2!$C$2:$M$2>=$I13),1))