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.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!