Lookup between a range

[CrustyR1]

Hi All,

I'm wanting to lookup a value from 2 separate cell values one being height and the other width - to find a match in a pricing matrix. If this can be done at all.
E.g - 717 x 448 = $36.06

[TABLE="width: 365"]
<tbody>[TR]
[TD][TABLE="width: 365"]
<tbody>[TR]
[TD]Qty[/TD]
[TD]Height[/TD]
[TD]Width[/TD]
[TD]Price EA[/TD]
[TD]Price Total[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]717[/TD]
[TD]448[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]357[/TD]
[TD]405[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]357[/TD]
[TD]405[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]720[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Matrix
[TABLE="width: 480"]
<tbody>[TR]
[TD="align: right"]WIDTH[/TD]
[TD]0 - 150mm[/TD]
[TD]151 - 300[/TD]
[TD]301 - 400[/TD]
[TD]401 - 450[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HEIGHT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100 - 300[/TD]
[TD="align: right"]$25.92[/TD]
[TD="align: right"]$26.85[/TD]
[TD="align: right"]$27.97[/TD]
[TD="align: right"]$28.52[/TD]
[/TR]
[TR]
[TD]301 - 400[/TD]
[TD="align: right"]$28.05[/TD]
[TD="align: right"]$27.97[/TD]
[TD="align: right"]$29.45[/TD]
[TD="align: right"]$30.20[/TD]
[/TR]
[TR]
[TD]401 - 450[/TD]
[TD="align: right"]$28.62[/TD]
[TD="align: right"]$28.52[/TD]
[TD="align: right"]$30.20[/TD]
[TD="align: right"]$31.04[/TD]
[/TR]
[TR]
[TD]451 - 600[/TD]
[TD="align: right"]$30.33[/TD]
[TD="align: right"]$30.20[/TD]
[TD="align: right"]$32.43[/TD]
[TD="align: right"]$33.55[/TD]
[/TR]
[TR]
[TD]601 - 750[/TD]
[TD="align: right"]$32.04[/TD]
[TD="align: right"]$31.87[/TD]
[TD="align: right"]$34.66[/TD]
[TD="align: right"]$36.06[/TD]
[/TR]
[TR]
[TD]751 - 900[/TD]
[TD="align: right"]$33.75[/TD]
[TD="align: right"]$33.55[/TD]
[TD="align: right"]$36.90[/TD]
[TD="align: right"]$38.57[/TD]
[/TR]
[TR]
[TD]901 - 1050[/TD]
[TD="align: right"]$35.46[/TD]
[TD="align: right"]$35.22[/TD]
[TD="align: right"]$39.13[/TD]
[TD="align: right"]$41.08[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 480"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Try changing the headers on your table slightly:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Qty	Height	Width	Price EA	Price Total			Matrix
2	2	717	448	$36.06	$72.12			WIDTH	0	151	301	401
3	3	357	405	$30.20	$90.60
4	3	357	405	$30.20	$90.60			HEIGHT
5	2	720	100	$32.04	$64.08			100	$25.92	$26.85	$27.97	$28.52
6								301	$28.05	$27.97	$29.45	$30.20
7								401	$28.62	$28.52	$30.20	$31.04
8								451	$30.33	$30.20	$32.43	$33.55
9								601	$32.04	$31.87	$34.66	$36.06
10								751	$33.75	$33.55	$36.90	$38.57
11								901	$35.46	$35.22	$39.13	$41.08
Sheet8

Cell Formulas
Range	Formula
D2	=INDEX($I$5:$L$11,MATCH(B2,$H$5:$H$11),MATCH(C2,$I$2:$L$2))
E2	=D2*A2

 

[CrustyR1]

Fantastic, thank you very much Eric

 

[Eric W]

Glad to help.