Look Up Using Multiple Criteria
Hello Smartsheet Community!
I am in need of some assistance in figuring out how to get the below accomplished. Any help is greatly appreciated!
Scenario: We have a massive project list. From time to time, we are provided a list with Store Numbers and an assigned Priority Number for one of the two scopes of the project (inside vs outside). The Store Numbers can exist in both project scopes. In our "master sheet" we use an Inside or Outside check box to call out which scope the store number is associated to for each row, as they are tracked separately.
Internally, we also have a unique "Project Number" that is assigned to each entry.
Goal: Pull the correct Priority Number into the Master Sheet for each store+scope combo. In this example, we are just working with stores that are part of the "inside" scope. Once I can get that figured out, I should be able to replicate it for the outside scope.
Struggle: I have been unable to figure out how to match up the store number to the correct scope so that we are pulling in the correct priority number. I have tried trying to use index/collect to pull in the project number to the Priority list so that I could use a "Synth" helper column that exists on both sheets, but I can't get it to work. What I tried I have left on the Priority List Sheets.
Master Sheet (Test Sheet A): https://app.smartsheet.com/b/publish?EQBCT=2a8f00cc49eb4021916f06af91e28095
Inside Priority List Sheet (Test Sheet E): https://app.smartsheet.com/b/publish?EQBCT=d108eef3be0248eab1adecd2005cd79f
Outside Priority List Sheet (Test Sheet F): https://app.smartsheet.com/b/publish?EQBCT=c6b84fc3ec294555a1db58ab7c98fafa
Help Article Resources
Check out the Formula Handbook template!