# Returning a value if multiple conditions are met

J

#### 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.

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.

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