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.


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

 

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