Lookup with hierarchy (multiple criteria)

[PrimeTimeAction]

I have a database sheet contain info regarding some paratemetrs. I also have a selection criteria table to get a "result value" based on parameter values. I want to make a lookup formula in the database sheet that goes through a series of criteria and gets the "result value" accordingly.
I tried to make a function using index-match and countif, but it couldn't get anywhere. Can somebody offer any advice?

The Database looks like this:

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Type[/TD]
[TD="class: xl65, width: 64"]Diameter[/TD]
[TD="class: xl65, width: 64"]Elevation[/TD]
[TD="class: xl65, width: 64"]Length[/TD]
[TD="class: xl65, width: 64"]Expected Result  from function[/TD]
[/TR]
[TR]
[TD="class: xl66"]Type A[/TD]
[TD="class: xl67, align: right"]10[/TD]
[TD="class: xl67, align: right"]3[/TD]
[TD="class: xl67, align: right"]6[/TD]
[TD="class: xl67"]Result 6[/TD]
[/TR]
[TR]
[TD="class: xl66"]Type B[/TD]
[TD="class: xl67, align: right"]20[/TD]
[TD="class: xl67, align: right"]-5[/TD]
[TD="class: xl67, align: right"]10[/TD]
[TD="class: xl68"]Result 7[/TD]
[/TR]
[TR]
[TD="class: xl66"]Type A[/TD]
[TD="class: xl67, align: right"]10[/TD]
[TD="class: xl67, align: right"]1[/TD]
[TD="class: xl67, align: right"]1.5[/TD]
[TD="class: xl68"]Result 1[/TD]
[/TR]
</tbody>[/TABLE]

The criteria/grade assigning table looks like this

 [TABLE="width: 505"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Diameter[/TD]
[TD]Elevation Min[/TD]
[TD]Elevation Max[/TD]
[TD]Length min[/TD]
[TD]Length Max[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Type A[/TD]
[TD]10[/TD]
[TD]-6[/TD]
[TD]6[/TD]
[TD]1.2[/TD]
[TD]1.6[/TD]
[TD]Result 1[/TD]
[/TR]
[TR]
[TD]Type A[/TD]
[TD]10[/TD]
[TD]-6[/TD]
[TD]6[/TD]
[TD]1.6[/TD]
[TD]3.2[/TD]
[TD]Result 2[/TD]
[/TR]
[TR]
[TD]Type A[/TD]
[TD]10[/TD]
[TD]-18[/TD]
[TD]-7[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]Result 3[/TD]
[/TR]
[TR]
[TD]Type A[/TD]
[TD]20[/TD]
[TD]-6[/TD]
[TD]6[/TD]
[TD]1.2[/TD]
[TD]1.6[/TD]
[TD]Result 4[/TD]
[/TR]
[TR]
[TD]Type A[/TD]
[TD]20[/TD]
[TD]-6[/TD]
[TD]6[/TD]
[TD]1.6[/TD]
[TD]3.2[/TD]
[TD]Result 5[/TD]
[/TR]
[TR]
[TD]Type A[/TD]
[TD]20[/TD]
[TD]-18[/TD]
[TD]-7[/TD]
[TD]3.2[/TD]
[TD]15[/TD]
[TD]Result 6[/TD]
[/TR]
[TR]
[TD]Type B[/TD]
[TD]20[/TD]
[TD]-7[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]13[/TD]
[TD]Result 7[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Hi, it's not clear to me what you want the results to be exactly.

Can you clarify please ?

 

[PrimeTimeAction]

Thanks for your reply. I mage to do it with a combination of SUMPRODUCT, INDEX and ROW functions.
This thread can be closed.