Help with IF/INDEX/MATCH (I think)
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!
Best Answers
-
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!
-
=if(has(collect({terminated},{entity name},[entity name]@row,{State of Filing},[State of Filing]@row),true),1,0)
Answers
-
=index({terminated},match([entity name]@row,{entity name},0))
You will have to build your intersheet connections
-
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!
-
=if(has(collect({terminated},{entity name},[entity name]@row,{State of Filing},[State of Filing]@row),true),1,0)
-
That worked perfectly. Thank you so much for your help!
-
Np glad I could help
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!