Return result from 3 criteria

[dazed and confused]

Hello,

I've tried a couple of things, but thought I'd ask online to see if its possible, or if there is an easier way of doing things. I'm trying to put a formulae/s together to get information from a price list. I've cut the list down as it actually goes up to 9.50m (901-950), but just so it's (hopefully) easier to view I've attached it up to 4.00m (351-400)

So entering a list of sizes
thickness into column R
width into column S
length into column T

If it is 30-141mm thick i want it to check the width (Column C), and then the length (Row 4) and give me the price for that size (blue costs)

If it is 142-450mm thick i want it to check the width (Column C), and then the length (Row 9) and give me the price for that size (red costs)

The current formulae i have doesn't work completely as it's only checking width and length. I also want it to use the price for that range of lengths, so for example if it is 150 x 190mm width, it would show prices from row 11, as it is it goes to the next price down.

-in cell U4, U5 & U6
=INDEX(D5:P16,MATCH(S4,C5:C16,1),MATCH(T4,D3:P3,1))

Am I over thinking this? Anyone's help would be appreciated.

1	A	B	C	D	E	F	G	H	I	Q	R	S	T	U	V
2	30 - 141mm thickness	Length (m)
3		Width		150	200	250	300	350	400		thickness	width	length	cost
4		from (mm)	to (mm)	000>150	151>200	201>250	251>300	301>350	351>400			200	300	90
5		50	300	100	200	300	400	500	600			200	350	100
6		301	+	-	-	-	-	-	-			300	350	120
7
8	142-450mm thickness	Length (m)
9				000>150	151>200	201>250	251>300	301>350	351>400
10		141	180	50	60	70	80	90	100
11		181	200	60	70	80	90	100	110
12		201	250	70	80	90	100	110	120
13		251	300	80	90	100	110	120	130
14		301	350	90	100	110	120	130	140
15		351	400	100	110	120	130	140	150
16		401	450	110	120	130	140	150	160

<tbody>
</tbody>

 

[Misca]

Welcome to the msg board!

I'd try something like

=INDEX(($D$4:$I$5,$D$9:$I$15),MATCH($L3,INDEX(($B$4:$B$5,$B$9:$B$15),,1,1+($K3>142))),MATCH($M3,$D$2:$I$2),1+($K3>142))

The formula would be a lot shorter if both your ranges used the same rows.

The formula basically consists of two INDEX formulas with area numbers. The inner one is used to pick the correct row and the outer picks the correct price list. The $K3>142 part would be better using a cell reference instead of a fixed value but it should work nevertheless. Also, the lengths on row 3 should be "Starting from" lengths instead of "Up to"s.

 

[dazed and confused]

Thanks for the welcome Misca, and for the amazing help!

I didn't think i'd get a reply that quickly so i'd carried on working away at it and started using the starting length rather than the upto length. But your formulae works a treat, i can stop banging my head against my desk now, THANKS!!