Help with IF/INDEX/MATCH (I think)

Beth Fantozzi 1

Hi there - I'm looking for a formula that looks at Sheet 2 and if the "Terminated" box is checked, then check the "Not Currently Active" box in Sheet 1, according to the entity name. (Meaning if Happy Co is listed as Terminated in Sheet 2, it would be marked as "Not Currently Active" in Sheet 1.

I appreciate any help you can offer!

Beth Fantozzi 1

I'm sorry but this didn't quite answer my question. And after I looked at it again, I realized that my question was wrong. I actually need to check the box "Not Currently Active" in Sheet 1 if the information in Sheet 2 is: "Terminated is Checked", and the state is X and the Entity Name is the same. Here are the new screen shots:

Thank you!

L_123

=if(has(collect({terminated},{entity name},[entity name]@row,{State of Filing},[State of Filing]@row),true),1,0)

L_123

=index({terminated},match([entity name]@row,{entity name},0))

You will have to build your intersheet connections

Beth Fantozzi 1

I'm sorry but this didn't quite answer my question. And after I looked at it again, I realized that my question was wrong. I actually need to check the box "Not Currently Active" in Sheet 1 if the information in Sheet 2 is: "Terminated is Checked", and the state is X and the Entity Name is the same. Here are the new screen shots:

Thank you!

L_123

=if(has(collect({terminated},{entity name},[entity name]@row,{State of Filing},[State of Filing]@row),true),1,0)

Beth Fantozzi 1

That worked perfectly. Thank you so much for your help!

L_123

Np glad I could help