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[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]D[/TD]
[TD]EFGH[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Product[/TD]
[TD]Desc[/TD]
[TD]Qty[/TD]
[TD]Unit Price[/TD]
[TD]total[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Code0001[/TD]
[TD]Product x[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]£4[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="width: 540"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 832"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Desc[/TD]
[TD]CODE[/TD]
[TD]1[/TD]
[TD]24.00[/TD]
[TD]49.00[/TD]
[TD]99.00[/TD]
[TD]199.00[/TD]
[TD]299.00[/TD]
[TD]399.00[/TD]
[TD]499.00[/TD]
[TD]750.00[/TD]
[TD]999.00[/TD]
[TD]1000.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Product x[/TD]
[TD]Code0001[/TD]
[TD]£1[/TD]
[TD]£2[/TD]
[TD]£3[/TD]
[TD]£4[/TD]
[TD]£5[/TD]
[TD]£6[/TD]
[TD]£7[/TD]
[TD]£8[/TD]
[TD]£9[/TD]
[TD]£10[/TD]
[TD]£11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="colspan: 4"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]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?[/TD]
[TD="colspan: 4"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

=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))