Return line's value based on multiple criteria

Michelle Campbell

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?

Michael Culley

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))))))