Looking up a value in columns based on two criteria

[MiguelS]

I have this sheet that has data in columns, qty break with price break. I want to pull the correct price (N2,P2,R2,T2,V2) based on teh Qty Due (M2) in my PODetails Sheet cOLUMN (BL)

If A2 matches in PO Details, give me the price in which the qty falls into.

For the future, I try to work on these, but I dont think I start the process correctly. How do you tackle these type of equations?

Thanks for any help.

	A	M	N	O	P	Q	R	S	T	U	V

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Part_Rev[/TD]
[TD="align: center"]Brk1[/TD]
[TD="align: center"]DUP Brk1[/TD]
[TD="align: center"]Brk2[/TD]
[TD="align: center"]DUP Brk2[/TD]
[TD="align: center"]Brk3[/TD]
[TD="align: center"]DUP Brk3[/TD]
[TD="align: center"]Brk4[/TD]
[TD="align: center"]DUP Brk4[/TD]
[TD="align: center"]Brk5[/TD]
[TD="align: center"]DUP Brk5[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]853-282223-001_A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]344[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]242[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]227[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]217[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]853-282263-002_A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]233[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]217[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]171[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]154[/TD]

</tbody>

QuotedLam​

	A	D	E	G	L	M	R	X	Y	Z	BL
Material_Part Rev	PO Number	PO Line	Material	Part Rev	Qty Due	Unit Price	Supplier Price*	Qty Due 1*	Supplier Due Date 1*	Qprice
853-267465-001_A	4501497640	853-267465-001	A	853-267465-001_A
04-436814-00_A	4501497709	04-436814-00	A	04-436814-00_A
04-436814-00_A	4501497709	04-436814-00	A	04-436814-00_A
03-450833-00_1	4501497709	03-450833-00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]20[/TD]

[TD="align: right"]1.00[/TD]
[TD="align: right"]47.59[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]3.00[/TD]
[TD="align: right"]3456.68[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]20[/TD]

[TD="align: right"]1.00[/TD]
[TD="align: right"]3456.68[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]30[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]300.18[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

PO Details​

 

[oldbrewer]

I cannot understand what you want. All I see is that price goes down with increasing quantity purchased.

853-282223-001_A does not occur in the lower table ?

 

[oldbrewer]

[TABLE="width: 1024"]
<colgroup><col width="64" span="16" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]item[/TD]
[TD]quantity[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]band1[/TD]
[TD]band2[/TD]
[TD]band3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]456-001[/TD]
[TD="align: right"]4000[/TD]
[TD]row 7[/TD]
[TD][/TD]
[TD][/TD]
[TD]123-456[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]130[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]456-002[/TD]
[TD="align: right"]230[/TD]
[TD="align: right"]215[/TD]
[TD="align: right"]210[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]456-001[/TD]
[TD="align: right"]375[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]340[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]789-003[/TD]
[TD="align: right"]975[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]850[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]item[/TD]
[TD]band1[/TD]
[TD]band2[/TD]
[TD]band3[/TD]
[TD]FINDER[/TD]
[TD]FINDER2[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]FINDER locates the column number that the quantity[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]123-456[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]3[/TD]
[TD]band3[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]slots in for EVERY item[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]456-002[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2750[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]2[/TD]
[TD]band2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]456-001[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]1[/TD]
[TD]band1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]FINDER2 locates the band for every item[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]789-003[/TD]
[TD="align: right"]2900[/TD]
[TD="align: right"]3250[/TD]
[TD="align: right"]7000[/TD]
[TD="align: right"]2[/TD]
[TD]band2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]the 3 under FINDER is obtained by[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]=MATCH($D$7,D21:F21,1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]band3 under FINDER2 is obtained by[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]so we find the band from the lower table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]=OFFSET($C21,-B21,G21)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]band1[/TD]
[TD][/TD]
[TD]formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]=OFFSET(OFFSET($C$20,MATCH(C7,$C$21:$C$24,0),0),0,5)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]so 4000 is not a big enough quantity to go to band 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MiguelS]

For some reason I cannot find the EDIT feature. But you are right, I should have made 853-267465-001_A on both tables. Replace 853-28223-001_1 with 853-267465-001_A on QuoteLam table. The value is there, but some where down the table on QuoteLam.

I want to take PO_Details A2 and search the QuoteLam array (table) (Column A). Once I locate that Row in quotedLam, I want to pull the correct Pricing based on the qty. For example, If the PO Details Column M2 is 1, I will put 344 from QuotedLam. If PO Details Column M2 is 4, I would pull 320 because Brk2 is 3 and Brk 3 is 5, so Qty 4 falls in between. If my QTY was 5, 6, 7, 8, 9 I would pull 242.

 

[MiguelS]

Seems like I got the correct Price Break now, maybe there is asimplier way to do this? I just need to be able to pull the correct row now to pull the correct data.

	Y

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]217[/TD]

</tbody>

PO Details​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Y2[/TH]
[TD="align: left"]=IF(M2<QuotedLam!O2, QuotedLam!N2, IF(M2<QuotedLam!Q2, QuotedLam!P2, IF(M2<QuotedLam!S2, QuotedLam!R2, IF(M2<QuotedLam!U2, QuotedLam!T2, IF(M2>=QuotedLam!U2,QuotedLam!V2,)))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]