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.

 

[J.Ty.]

Hi,
I have made a solution. It is based on the assumption that on the price list width is alsways greater than length.

Book2
	A	B	C	D	E	F	G
1	Length (cm)	Width (cm)	PRICE		L	W	Price
2	25	35	200		12	64	350
3	35	50	300
4	33	70	350
5	41	57	400
6	46	64	500
7	50	70	600
8	57	82	800
9	64	90	1000
10	70	100	1200
Sheet2

Cell Formulas
Range	Formula
G2	G2	=INDEX(C1:C10,MIN(IF(A2:A10>=MIN(E2,F2),IF(B2:B10>=MAX(E2:F2),ROW(A2:A10),1000),1000)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Beatrice]

Thanks J.Ty

I have tried to adapt exactly the same,but it turns out different answer.
do you know why?

I uploaded the file to wetransfer.com : 5445627.xlsx
if you don't mind, could you please let me know what I have done wrong?
Thanks a lot.

 

[J.Ty.]

I have made the necessary correction, as shown below. The problem was that I used ROW function, which returns the row number of the cell which is the argument. In general, formulas which use ROW (and analogous COLUMN function) can be affected by changes made to the spreadsheet design, like relocating the data. I have made the formula independent of this.
Additionally, I have added a mechanism which gives a polite answer "not available" instead of an error if the requested object is larger than anything you have in your data.

At the moment the formula returns the first item (from top) which matches the input size, so it will be the cheapest one. If you want any alternative mechanism, please let me know.

The spreadsheet is available here.

Beatrice.xlsx
	B	C	D
1	Size range (cm)		Price
2	101	60	not available
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

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.

 

[Beatrice]

Thank you, it works perfectly.
just wonder what is "10^7"? I want to understand so I can use it for future.
Thanks a lot

 

[J.Ty.]

10^7 is 10000000 and is returned in place of a row number in case the item is too small. MIN chooses the smallest row number of all, so if all items are too small, you get 10^7 which i more than any row number in an Excel worksheet. That guarantees that you do not get an accidental row from your data.

If you want to understad how this all works, you can use formula evaluator. Select the cell with your formula, go to Ribbon -> Formulas-> Evaluate Formula (icon with a magnifying glass with fx inside) and click. A small window appears with your formula. With each click on the Evaluate button, you can watch how the subformulas of your formula turn into values. Very instructive and helpful.

 

[Beatrice]

Thank you for your explanation, indeed much thanks for being so patient with me.
I still have a lot learn obviously ?

Have a good day

 

[J.Ty.]

Thanks for the feedback and good luck!

J.Ty.

 

[Peter_SSs]

Thank you, it works perfectly.

That doesn't seem to agree with ..

another example 45 x 75 will have to take PRICE 1200

What have I missed?

 

[J.Ty.]

That doesn't seem to agree with ..
What have I missed?

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