If/ case statement help please

[Imran Azam]

​Hi guys

i have the data below which defines the rule of YES/NO and Final Outcome

name set status action YES/NO Final Outcome TRUE TRUE yes install FALSE TRUE yes close TRUE FALSE no fix TRUE TRUE no freeze yes leave <tbody> </tbody>

<tbody>
</tbody>

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

system	name	set	status	action	yes/no	final outcome
1	TRUE	FALSE	FALSE	FALSE

<tbody>
</tbody>

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

<tbody>
</tbody>