If/ case statement help please

[Imran Azam]

​Hi guys

i have the data below which defines the rule of YES/NO and Final Outcome
[TABLE="width: 414"]
<tbody>[TR]
[TD][TABLE="width: 414"]
<tbody>[TR]
[TD="align: center"]name[/TD]
[TD="align: center"]set[/TD]
[TD="align: center"]status[/TD]
[TD="align: center"]action[/TD]
[TD="align: center"]YES/NO[/TD]
[TD="align: center"]Final Outcome[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]yes[/TD]
[TD="align: center"]install[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]yes[/TD]
[TD="align: center"]close[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]no[/TD]
[TD="align: center"]fix[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"][/TD]
[TD="align: center"]no[/TD]
[TD="align: center"]freeze[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]yes[/TD]
[TD="align: center"]leave[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need a If statement or another formula to pick up all the above rules to the below data to pick up the correct YES/NO and Final Outcome fields

[TABLE="width: 508"]
<tbody>[TR]
[TD="align: center"]system[/TD]
[TD="align: center"]name[/TD]
[TD="align: center"]set[/TD]
[TD="align: center"]status[/TD]
[TD="align: center"]action[/TD]
[TD="align: center"]yes/no[/TD]
[TD="align: center"]final outcome[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
the formula needs to pick up no for the yes/no field and fix for final outcome field

can this be done?

 

[Imran Azam]

Anyone?

 

[Tetra201]

Try these:

=VLOOKUP(SUMPRODUCT((B2:E2)*{1,2,4,8}),{10,"yes";2,"yes";1,"no";6,"no";0,"yes"},2,0) for yes/no
=VLOOKUP(SUMPRODUCT((B2:E2)*{1,2,4,8}),{10,"install";2,"close";1,"fix";6,"freeze";0,"leave"},2,0) for final outcome

 

[Imran Azam]

Try these:

=VLOOKUP(SUMPRODUCT((B2:E2)*{1,2,4,8}),{10,"yes";2,"yes";1,"no";6,"no";0,"yes"},2,0) for yes/no
=VLOOKUP(SUMPRODUCT((B2:E2)*{1,2,4,8}),{10,"install";2,"close";1,"fix";6,"freeze";0,"leave"},2,0) for final outcome

thank you for this but this will only work if its stays like this I want it dynamic , so if the fields are changed it picks the correct data for the yes/no and final outcome fields, thank you for your help [TABLE="class: cms_table, width: 508"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]