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
1	Part_Rev	Brk1	DUP Brk1	Brk2	DUP Brk2	Brk3	DUP Brk3	Brk4	DUP Brk4	Brk5	DUP Brk5
2	853-282223-001_A	1	344	3	320	5	242	10	227	15	217
3	853-282263-002_A	1	233	3	217	5	171	10	161	15	154

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

QuotedLam​

	A	D	E	G	L	M	R	X	Y	Z	BL
1	Material_Part Rev	PO Number	PO Line	Material	Part Rev	Qty Due	Unit Price	Supplier Price*	Qty Due 1*	Supplier Due Date 1*	Qprice
2	853-267465-001_A	4501497640	20	853-267465-001	A	1.00	47.59	853-267465-001_A
3	04-436814-00_A	4501497709	10	04-436814-00	A	3.00	3456.68	04-436814-00_A
4	04-436814-00_A	4501497709	20	04-436814-00	A	1.00	3456.68	04-436814-00_A
5	03-450833-00_1	4501497709	30	03-450833-00	1	2.00	300.18	#N/A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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]

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

<colgroup><col width="64" span="16" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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
2	217

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

PO Details​

Worksheet Formulas Cell Formula Y2 =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,))))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>