Return line's value based on multiple criteria
Hello,
I am looking for help in returning a particular cell's contents based on multiple criteria. I have two sheets I am working with, Primary Sheet and Data Sheet, where I will be searching for the criteria. Below is a snippet of some dummy data.
I would like to write a formula where I return the value of the Work ID field from the Data Sheet into the Work ID field in the Primary Sheet that matches the corresponding Project ID based on the criteria on both sheets. My criteria are: Criteria 1 - Site Type = Ext and Site Sub Type = Extension or Criteria 2 - Site Type = Sol and Site Sub Type = Solid.
I began using IF and Collect functions, but was unsuccessful in having Smartsheets return a particular value based off the two criteria matches.
Have others encountered something similar and can point me in the right direction?
Answers
-
So basically something like this?:
=if([site type]@row="Ext",if(index({site sub type},match([project ID]@row,{project ID},0))="Extension",index({Work ID},match([project ID]@row,{project ID},0)),if([site type]@row="Sol",if(index({site sub type},match([project ID]@row,{project ID},0))="Solid",index({Work ID},match([project ID]@row,{project ID},0))))))
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
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!