Finding a price in a price table

[Rammaz94]

For a table like this;

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Width[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Drop[/TD]
[TD]1000[/TD]
[TD]1200[/TD]
[TD]1400[/TD]
[TD]1600[/TD]
[TD]1800[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]£5[/TD]
[TD]£10[/TD]
[TD]£15[/TD]
[TD]£20[/TD]
[TD]£25[/TD]
[TD]£30[/TD]
[/TR]
[TR]
[TD]1500[/TD]
[TD]£10[/TD]
[TD]£15[/TD]
[TD]£20[/TD]
[TD]£25[/TD]
[TD]£30[/TD]
[TD]£35[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]£15[/TD]
[TD]£20[/TD]
[TD]£25[/TD]
[TD]£30[/TD]
[TD]£35[/TD]
[TD]£40[/TD]
[/TR]
</tbody>[/TABLE]

How could I type in a width x drop and return a price

eg:

Width Drop Price
1050 1300 £15

 

[etaf]

how does that work exactly
width = 1050
so does it use the closest higher value - so using the 1200 column
and
drop
1300
so again nearest higher value - so the 1500 row
intersect is £15

is so could the table be backwards

see attached
=INDEX($J$2:$O$4, MATCH(A2,$I$2:$I$4,-1), MATCH(B2,$J$1:$O$1,-1))

https://www.dropbox.com/s/a6q2mutj5sm2bw4/etaf_lookup.xlsx?dl=0

 

[Rammaz94]

Yeah, it would move to the nearest higher value.

So I would need to reverse the widths and drops to go from highest to lowest?

 

[etaf]

if you open the link i posted , you will see it working
Its probably possible to do it without changing the table order - but that was the first thing that came to mind
other members may have a better solution

 

[Mike Szczesny]

Can you use something like this? Hope this helps.

Excel 2012
	A	B	C	D	E	F	G	H
1			1000	1200	1400	1600	1800	2000
2		1000	1	2	3	4	5	6
3		1500	7	8	9	10	11	12
4		2000	13	14	15	16	17	18
5
6	Width	1050		8
7	Drop	1300
Sheet1

Cell Formulas
Range	Formula
D6	{=SMALL(IF($C$1:$H$1>=$B$6,IF($B$2:$B$4>=$B$7,$C$2:$H$4)),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.