Mos efficient way to get a value from a table given three logical tests

[Kershan999]

The goal is to use a formula to get a particular e_c value in a cell and a particular e_a value in a another cell given the three logical tests which are E_a (either E_a < 0.5E_C or E_a > 0.5E_C), Class (1,2 or 3) and Repair Lifetime (Any Value rounded up to 2, 10 or 20 years). I understand that I can combine the IF and AND function but that will create a very long and tedious formula. Hence, I am looking for an alternative way to do that.

I'm open to other suggestions like drop downs and what not. Hope you may help me solve this problem. Looking forward to getting your replies.

Thank you.



[TABLE="class: grid, width: 1040"]
<tbody>[TR]
[TD][/TD]
[TD]E_a[/TD]
[TD]Class[/TD]
[TD]Repair Lifetime[/TD]
[TD]e_c[/TD]
[TD]e_a2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]E_a < 0.5E_C[/TD]
[TD]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0.1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]E_a < 0.5E_C[/TD]
[TD]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0.27[/TD]
[TD="align: right"]0.1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]E_a < 0.5E_C[/TD]
[TD]3[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]0.1[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]E_a < 0.5E_C[/TD]
[TD]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.35[/TD]
[TD="align: right"]0.1[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]E_a < 0.5E_C[/TD]
[TD]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0.1[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]E_a < 0.5E_C[/TD]
[TD]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]0.1[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]E_a < 0.5E_C[/TD]
[TD]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.25[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]E_a < 0.5E_C[/TD]
[TD]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0.32[/TD]
[TD="align: right"]0.16[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]E_a < 0.5E_C[/TD]
[TD]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]0.1[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]E_a > 0.5E_C[/TD]
[TD]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0.3[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]E_a > 0.5E_C[/TD]
[TD]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0.27[/TD]
[TD="align: right"]0.27[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]E_a > 0.5E_C[/TD]
[TD]3[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]0.25[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]E_a > 0.5E_C[/TD]
[TD]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.35[/TD]
[TD="align: right"]0.35[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]E_a > 0.5E_C[/TD]
[TD]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0.3[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]E_a > 0.5E_C[/TD]
[TD]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]0.25[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]E_a > 0.5E_C[/TD]
[TD]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.4[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]E_a > 0.5E_C[/TD]
[TD]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0.32[/TD]
[TD="align: right"]0.32[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]E_a > 0.5E_C[/TD]
[TD]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]0.25[/TD]
[/TR]
</tbody>[/TABLE]

 

[mart37]

you can use SUMPRODUCT((condition1)*(condition2)*(condition3);searchrange)

 

[Kershan999]

Thanks for the reply mart37 but I do not intend to multiply condition 1, 2 and 3. My objective is to pick out one value each from e_c and e_a2 columns (each of these columns have a range of 18 values as can be seen on the table above), given the three logical tasks which are E_a, Class, and Repair Lifetime (These are the conditions required to pick out one value each from the e_c and e_a2 columns). I hope you understand my request and I hope to hear from you soon. Thank you.

 

[mart37]

The * isn't the symbol for multiply in a sumproduct()!
It means AND.
You want to search Condition 1 = true AND Condition 2 = true AND Condition 3 = true
As the result is one item then you can use SUMPRODUCT().