Returning a value if multiple conditions are met

[Joel]

I am trying to develop a method which will return a value if each of three
conditions are met. The three conditions, each with a differing number of
possibilities, are set in three columns and for each permutation there is a
value associated with it.
I would then like to set up a drop down list for each condition from which
to select the possibility of each and have the value for that combination of
conditions returned to an adjacent cell.
Example:
Conditions >> A B C Value
possibilities 1 x a 1
1 y a 2
1 x b 3
1 y b 4
2 x a 5
2 y a 6
2 x b 7
2 y b 8


Select: A B C Result
Any help with this problem would be greatly appreciated.

 

[Steve Dunn]

Assuming your table in A29, and your criteria in F2:H2

=index($D$2:$D$8,match(1,($A$2:$A$8=$F$2)*
($B$2:$B$8=$G$2)*($C$2:$C$8=$H$2,0))

should give the correct result.

 

[Ashish Mathur]

Hi,

Try this. A2:C9 is your data range. A15:C15 is the select table

=sumproduct(($A$2:$A$9=$A15)*($B$2:$B$9=$B15)*($C$2:$C$9=$C15)*($D$2:$D$9))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com