Vlookup multiple criteria aproximate value

[forzasec]

Hello,

I have been checking the forum and I haven'found a solution to this question.

I cut cardboards to the client requests and I have to calculate a price. Here is the example:

Cardboards (cm)
40x30 -> 3,5€
40x40 -> 4€
60x40 -> 5€
60x60 -> 5,5€

the client can request a 57x15 cardboard, so the price is 5€ because I have to sell the 60x40 cardboard. If they ask to me a 57x57 cardboard, I have to sell the 60x60 cardboard that is 5,5€.

I can not construct a formula to obtain the price, even with vlookup, match, index, and so on. Is there a formula to be applied or I have to create a macro?

Thanks in advance.

 

[Toadstool]

Hi Forzasec,

Does this do what you want?

Forzasec.xlsx
	C	D	E	F	G	H	I	J
1	Customer Request			Result		Cardboard (cm)
2	X	Y		€ 5.50		X	Y	Price
3	42	41				40	30	€ 3.50
4						40	40	€ 4.00
5						60	40	€ 5.00
6						60	60	€ 5.50
Sheet1

Cell Formulas
Range	Formula
F2	F2	=IFERROR(INDEX($J$3:$J$6,AGGREGATE(15,6,ROW($H$3:$H$6)-ROW($H$2)/(($H$3:$H$6>=$C$3)*($I$3:$I$6>=$D$3)),1)),"Too big")

 

[forzasec]

Thank you, very professional.
I have also solved transforming my price table to a matrix in order to use the formulas "index" and "match".



But your solution is better because is more natural to me the price list as the one you have used.

Thank you again.