Looking for the purchase price based on the purchase quantity

[Vegas01]

Morning everyone

I am having problem with this one.

I am trying to compare pricing on an invoice to the price list.

On the invoice I have the Item Code Column A and purchase quantity Column C and the Invoice Price in Column G

On the price list there are a number of prices for a product, the price charged is determined by the quantity purchased - on the price list I have the Item Code Column A, the Purchase quantity in Column D and the Price to be charged in Column C See tables below :


So I need to look up the Item Code on the Price list and return the Sale Price for the quantity that has been purchased - for the example below I need to return $7.22

I hope this makes sense. Thanks in advance

Invoice Details

Product Code	Product	Invoice Qty	Document Type		Invoice Date	Invoice Wholesale Price	Invoice Net Price
10033333	Product A	6	Invoice		20230724	7.95	5.85

Price List

Material ID	Material Description	Item Best Price (per single)	Item Best Price Scale	MOQ	State Based Price per single
10033333	Product A	$ 8.02	1	1	$ 8.18
10033333	Product A	$ 7.22	3	1	$ 8.18

 

[keda duck]

For the example, why should return $7.22? Can you clarify it?

 

[Anonymous1378]

Try this?

Book20
	A	B	C	D	E	F	G	H	I
1	Invoice Details
2	Product Code	Product	Invoice Qty	Document Type		Invoice Date	Invoice Wholesale Price	Invoice Net Price
3	10033333	Product A	6	Invoice		20230724	7.95	5.85	7.22
4
5	Price List
6	Material ID	Material Description	Item Best Price (per single)	Item Best Price Scale	MOQ	State Based Price per single
7	10033333	Product A	$8.02	1	1	$8.18
8	10033333	Product A	$7.22	3	1	$8.18
Sheet1

Cell Formulas
Range	Formula
I3	I3	=INDEX(C7:C8,MATCH(1,(A7:A8=A3)*(D7:D8=MAX(IF((A7:A8=A3)*(D7:D8<C3),D7:D8,0))),0))

 

[Vegas01]

=INDEX(C7:C8,MATCH(1,(A7:A8=A3)*(D7:D8=MAX(IF((A7:A8=A3)*(D7:D8<C3),D7:D8,0))),0))

Brilliant thanks a lot