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

Qty Height Width Price EA Price Total 2 717 448 3 357 405 3 357 405 2 720 100 <tbody> </tbody>

<tbody>
</tbody>

Matrix

WIDTH	0 - 150mm	151 - 300	301 - 400	401 - 450
HEIGHT
100 - 300	$25.92	$26.85	$27.97	$28.52
301 - 400	$28.05	$27.97	$29.45	$30.20
401 - 450	$28.62	$28.52	$30.20	$31.04
451 - 600	$30.33	$30.20	$32.43	$33.55
601 - 750	$32.04	$31.87	$34.66	$36.06
751 - 900	$33.75	$33.55	$36.90	$38.57
901 - 1050	$35.46	$35.22	$39.13	$41.08

<tbody>
</tbody>

<tbody>
</tbody>

 

[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.