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.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 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!