Price lookup based on width x drop values

[Ketreikan]

HI All, wondering if I can get some help?
I'm trying to create a excel sheet which will automatically return a price based on a given width and drop value. Currently, I calculate the price manually by looking in a price book which has plenty of rows and columns and prices. I want to simplify this by simply entering the dimensions so it automatically calculates price based on the dimensions entered.
Below is the start of my worksheet. If I choose the exact sizes shown on the table, it will return a price, however if I choose a size that is not listed, I get an #N/A. Eg. If I choose 780 x 1500, it will return the price $179, but if I choose for instance 775 x 1490, it only returns #N/A, when I want it to still return the price $179.
My formula in K3 is
=IF(AND($I$3>0,$J$3>0),INDEX($A$3:$F$8,MATCh(J3,$A$3:$A$8,0),MATCH(I3,$A$3:$F$3,0)))

Can anyone help?
Thanks.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1
2								Item	Width	Height	Price
3	mm	630	780	930	1080	1230		1	975	1821	#N/A
4	900	158	168	179	189	199		2
5	1200	162	174	185	195	208		3
6	1500	166	179	191	203	216
7	1800	170	185	197	210	222
8	2100	174	189	203	216	231
9	2400	179	193	208	224	239
10	2700	185	201	218	233	249
11
Sheet1

Excel 2010

Cell Formulas
Range	Formula
K3	=IF(AND($I$3>0,$J$3>0),INDEX($A$3:$F$8,MATCH(J3,$A$3:$A$8,0),MATCH(I3,$A$3:$F$3,0)))

 

[AlphaFrog]

If you change your formula to this, it will match the closest values less than or euqal to the lookup values.

=IF(AND($I$3>0,$J$3>0),INDEX($A$3:$F$8,MATCH(J3,$A$3:$A$8,1),MATCH(I3,$A$3:$F$3,1)))

You would have to change the cutoff limits in the table as below.

<br />

Book1
	A	B	C	D	E	F	G	H	I	J	K
2								Item	Width	Height	Price
3	mm	0	631	781	931	1081	1231	1	775	1490	179
4	0	158	168	179	189	199		2
5	901	162	174	185	195	208		3
6	1201	166	179	191	203	216
7	1501	170	185	197	210	222
8	1801	174	189	203	216	231
9	2101	179	193	208	224	239
10	2401	185	201	218	233	249
11	2701
Sheet1

Cell Formulas
Range	Formula
K3	=IF(AND($I$3>0,$J$3>0),INDEX($A$3:$F$8,MATCH(J3,$A$3:$A$8,1),MATCH(I3,$A$3:$F$3,1)))

Alternatively, you could keep the table limits as before but the formula adds 1 to the Width and Height lookup values.

 

[Ketreikan]

Alpha Frog, Thank you so much for your help... It worked perfectly.
I have one other query...
I am copying the formula down to cells L4 & L5 etc...
When I have no value in the width and drop cells, I still get a min price of $158 in the L cells... Is there any way to have these as 0, or blank until I put sizing in?

Thanks so much for your help.

Regards

Ket.

 

[AlphaFrog]

You're welcome.

I have one other query...
I am copying the formula down to cells L4 & L5 etc...
When I have no value in the width and drop cells, I still get a min price of $158 in the L cells... Is there any way to have these as 0, or blank until I put sizing in?

The IF(AND($I$3>0,$J$3>0)... should prevent that if you remove the absolute $'s. You may want to add a null string ("") for the FALSE result of the IF as below.

=IF(AND(I3>0,J3>0),INDEX($A$3:$F$8,MATCH(J3,$A$3:$A$8,1),MATCH(I3,$A$3:$F$3,1)),"")

 

[Ketreikan]

AlphaFrog, You truly are a legend.
Thank you so much for your help.

Regards

Ket.