Find from list

[Beatrice]

greeting, I need some help to look for the related prices, example list as below:

Length (cm)	Width (cm)	PRICE
25	35	200​
35	50	300​
33	70	350​
41	57	400​
46	64	500​
50	70	600​
57	82	800​
64	90	1000​
70	100	1200​

I need a formula not only look for the exact value, but also include the odd sizes not from the list. (equal or greater than)
for example, 60 x 85 should take PRICE at 1000.
another example 45 x 75 will have to take PRICE 1200 (rotated to fit)

Thank you in advance for helping.

 

[Peter_SSs]

It seems an obvious mistake in manual evaluation, so I decided to ignore it.

Perhaps so, though I thought it worth asking the OP.

@Beatrice
Assuming it was just an error in your expected result that I asked about earlier, this may be another option for you as it enables the formula to be somewhat simpler & with standard entry instead of Ctrl+Shift+Enter, provided it is acceptable to arrange your table differently.
Sort the table in descending order instead of ascending and include a "Not available" row at the top with values larger than you will ever want to look up.

20 03 13.xlsm
	B	C	D
1	Size range (cm)		Price
2	85	60	1000
3
4	Size range (cm)		Price
5	1000000	1000000	Not available
6	70	100	1200
7	64	90	1000
8	57	82	800
9	50	70	600
10	46	64	500
11	41	57	400
12	35	50	300
13	33	70	350
14	25	35	200
Price

Cell Formulas
Range	Formula
D2	D2	=INDEX(D5:D14,MIN(MATCH(MIN(B2:C2),B5:B14,-1),MATCH(MAX(B2:C2),C5:C14,-1)))

 

[J.Ty.]

Perhaps so, though I thought it worth asking the OP.

@Beatrice
Assuming it was just an error in your expected result that I asked about earlier, this may be another option for you as it enables the formula to be somewhat simpler & with standard entry instead of Ctrl+Shift+Enter, provided it is acceptable to arrange your table differently.
Sort the table in descending order instead of ascending and include a "Not available" row at the top with values larger than you will ever want to look up.

Peter,

You are making a silent assumption that the sizes can be sorted so that both width and length decrease. This might be impossible, e.g. take 10x12 and 9x14.
I admit making an assumption too: that length is never greater than width.

@Beatrice, please comment or at consider.

J.Ty.

 

[Peter_SSs]

You are making a silent assumption that the sizes can be sorted so that both width and length decrease.

That is one way of looking at it. My actual assumption was that the OP's data was representative.

After all, if the data cannot be sorted appropriately, how would you logically decide the correct answer?
For example, in the sample below, your formula gives 220 but rows 7 & 8, though still not exact, to me both look better fits to the data than row 6.

20 03 13.xlsm
	B	C	D
1	Size range (cm)		Price
2	10	100	220
3
4	Size range (cm)		Price
5	8	50	200
6	40	100	220
7	10	101	350
8	9	100	400
9	46	130	500
10	60	140	600
11	62	150	800
12	64	160	1000
13	70	170	1200
Price 3

Cell Formulas
Range	Formula
D2	D2	=IFERROR(INDEX(D5:D13,MIN(IF(B5:B13>=MIN(B2,C2),IF(C5:C13>=MAX(B2:C2),ROW(B5:B13)-ROW(D4),10^7),10^7))),"not available")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[J.Ty.]

OK, I have made another variant, which works without any particular assumptions about the data and returns the cheapest option available. The answer 0 means that there is no available item of suitable size.

Beatrice.xlsx
	B	C	D
1	Size range (cm)		Price
2	70	101	0
3
4	Size range (cm)		Price
5	25	35	200
6	35	50	300
7	33	70	350
8	41	57	400
9	46	64	500
10	50	70	600
11	57	82	800
12	64	90	1,000
13	70	100	1,200
MrExcel (2)

Cell Formulas
Range	Formula
D2	D2	=MIN(IF((B5:B13>=B2)*(C5:C13>=C2)+(B5:B13>=C2)*(C5:C13>=B2),D5:D13,""))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Beatrice]

@Peter_SSs , @ J.Ty.
Thanks for helping. After read above comment, I should clarify that:
1. The table must be ascending,
2. max size will be 70 x 100, but width and length could interchange.

I used the format provided by J.Ty
{=IFERROR(INDEX(D5:D13,MIN(IF(B5:B13>=MIN(B2,C2),IF(C5:C13>=MAX(B2:C2),ROW(B5:B13)-ROW(D4),10^7),10^7))),"not available")}
that was workable.

 

[Peter_SSs]

I used the format provided by J.Ty
{=IFERROR(INDEX(D5:D13,MIN(IF(B5:B13>=MIN(B2,C2),IF(C5:C13>=MAX(B2:C2),ROW(B5:B13)-ROW(D4),10^7),10^7))),"not available")}
that was workable.

OK, glad you got something that suited you.