Quote Creation Document - Requiring double lookup

LyndseyK

New Member
Joined
Oct 17, 2019
Messages
2
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
DEFGHIJK
12ProductDescQtyUnit Pricetotal
13Code0001Product x22£4

<tbody>
</tbody>

Sheet 2
ABCDEFGHIJKLM
2DescCODE124.0049.0099.00199.00299.00399.00499.00750.00999.001000.00
3Product xCode0001£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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,878
Office Version
365
Platform
Windows
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

New Member
Joined
Oct 17, 2019
Messages
2
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,878
Office Version
365
Platform
Windows
Replace the formula you have in J13 with the one I suggested.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Desc</td><td style=";">CODE</td><td style="text-align: right;;">1</td><td style="text-align: right;;">24</td><td style="text-align: right;;">49</td><td style="text-align: right;;">99</td><td style="text-align: right;;">199</td><td style="text-align: right;;">299</td><td style="text-align: right;;">399</td><td style="text-align: right;;">499</td><td style="text-align: right;;">750</td><td style="text-align: right;;">999</td><td style="text-align: right;;">1000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Product x</td><td style=";">Code0001</td><td style="text-align: right;;">£1</td><td style="text-align: right;;">£2</td><td style="text-align: right;;">£3</td><td style="text-align: right;;">£4</td><td style="text-align: right;;">£5</td><td style="text-align: right;;">£6</td><td style="text-align: right;;">£7</td><td style="text-align: right;;">£8</td><td style="text-align: right;;">£9</td><td style="text-align: right;;">£10</td><td style="text-align: right;;">£11</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style=";">Code0002</td><td style="text-align: right;;">£10</td><td style="text-align: right;;">£11</td><td style="text-align: right;;">£12</td><td style="text-align: right;;">£13</td><td style="text-align: right;;">£14</td><td style="text-align: right;;">£15</td><td style="text-align: right;;">£16</td><td style="text-align: right;;">£17</td><td style="text-align: right;;">£18</td><td style="text-align: right;;">£19</td><td style="text-align: right;;">£20</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Product</td><td style=";">Desc</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Qty</td><td style=";">Unit Price</td><td style=";">total</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Code0001</td><td style=";">Product x</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">£4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Code0002</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td><td style="text-align: right;;">11</td><td style="text-align: right;;">£264</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J13</th><td style="text-align:left">=INDEX(<font color="Blue">Sheet2!$C$3:$M$13,MATCH(<font color="Red">D13,Sheet2!$B$3:$B$13,0</font>),AGGREGATE(<font color="Red">15,6,(<font color="Green">COLUMN(<font color="Purple">Sheet2!$C$2:$M$2</font>)-COLUMN(<font color="Purple">Sheet2!$C$2</font>)+1</font>)/(<font color="Green">Sheet2!$C$2:$M$2>=$I13</font>),1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Forum statistics

Threads
1,085,878
Messages
5,386,509
Members
402,003
Latest member
johnpearson67

Some videos you may like

This Week's Hot Topics

Top