How to expand INDEX/MATCH for multiple data ranges across many related columns

[newty]

Hello
I am looking for a functions that can return a value from one column after matching several criteria in related columns. (Win/Excel 07)

In the example below I would need a score of 2.0000 returned after matching the criteria entered on the right to the ranges in the reference table.

Thanks for any help.
[TABLE="width: 704"]
<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]
[/TR]
[TR]
[TD]Gender[/TD]
[TD]Start Age[/TD]
[TD]End Age[/TD]
[TD]Start Value[/TD]
[TD]End Value[/TD]
[TD="class: xl63"]Score[/TD]
[TD][/TD]
[TD]Gender[/TD]
[TD]Age[/TD]
[TD]Value[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD="class: xl63"]M[/TD]
[TD="class: xl63"]20[/TD]
[TD="class: xl63"]25[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]0.499[/TD]
[TD="class: xl64"]1.0000[/TD]
[TD][/TD]
[TD="class: xl66"]M[/TD]
[TD="class: xl66"]27[/TD]
[TD="class: xl66"]0.65[/TD]
[TD="class: xl67"]?[/TD]
[/TR]
[TR]
[TD="class: xl63"]M[/TD]
[TD="class: xl63"]26[/TD]
[TD="class: xl63"]30[/TD]
[TD="class: xl65"]0.500[/TD]
[TD="class: xl65"]0.999[/TD]
[TD="class: xl64"]2.0000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]M[/TD]
[TD="class: xl63"]31[/TD]
[TD="class: xl63"]35[/TD]
[TD="class: xl65"]1.000[/TD]
[TD="class: xl65"]1.499[/TD]
[TD="class: xl64"]3.0000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]M[/TD]
[TD="class: xl63"]36[/TD]
[TD="class: xl63"]40[/TD]
[TD="class: xl65"]1.500[/TD]
[TD="class: xl65"]1.999[/TD]
[TD="class: xl64"]4.0000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]F[/TD]
[TD="class: xl63"]20[/TD]
[TD="class: xl63"]25[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"]0.499[/TD]
[TD="class: xl64"]10.0000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]F[/TD]
[TD="class: xl63"]26[/TD]
[TD="class: xl63"]30[/TD]
[TD="class: xl65"]0.500[/TD]
[TD="class: xl65"]0.999[/TD]
[TD="class: xl64"]11.0000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]F[/TD]
[TD="class: xl63"]31[/TD]
[TD="class: xl63"]35[/TD]
[TD="class: xl65"]1.000[/TD]
[TD="class: xl65"]1.499[/TD]
[TD="class: xl64"]12.0000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]F[/TD]
[TD="class: xl63"]36[/TD]
[TD="class: xl63"]40[/TD]
[TD="class: xl65"]1.500[/TD]
[TD="class: xl65"]1.999[/TD]
[TD="class: xl64"]13.0000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RCBricker]

in your example the ages category does not overlap so all you need it to ensure that the gender and age categories are accounted for.

=SUMPRODUCT((A14:A21=H14)*(B14:B21<=I14)*(C14:C21>=I14)*F14:F21)

****Note change ranges to reflect the ranges of your data.

Rich

 

[newty]

Excellent - thank you. Great idea I would never of thought of using sum product. everything multiplied by 0 = 0