Look Up Using Multiple Criteria

Nancy Heater

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

Kelly Moore

Hey Nancy

I added the @cell= to your row criteria in your [Project Number] Index Collect. See Sheet E.

=INDEX(COLLECT({Test Sheet A Range 1}, {Test Sheet A Range 2}, @cell = STORE@row, {Test Sheet A Range 3}, @cell = INSIDE@row), 1)

It seems to do the trick. I can see the corresponding Priorities being collected in Sheet A against these Sheet E projects

I left Sheet F for you to test.

Let me know.

Kelly

Kelly Moore

Hello @Nancy Heater

Your Inside and Outside sheets have their published link permissions set to inside your organization and are not accessible. Please be sure that sensitive info is removed and change the access setting to make it viewable if you think we need more than a screenshot of your {Test Sheet E Range 1} and {Test Sheet E Range 2} columns. Based on your current formula, your Synth column should be an exact match to the values in {Test Sheet E Range 2}. Is your Synth column an exact match?

Nancy Heater

Good morning @Kelly Moore -

Thanks! I have adjusted the settings on the sheets (sorry about that). These contain dummy data to represent examples of what we're trying to do.

The Synth columns are not an exact match because they need to combine the Store Number and Project Number. We don't have the project numbers on the E and F sheets for the same reasons listed in my description below. We've been unable to get a index/match or index/collect formula to work to make sure the correct info is pulled in based on the inside vs outside check boxes on the master sheet.

Thanks!

Brent Wilson

@Nancy Heater the issue is the one-to-many relationships

If you know that you will only have one exterior and one interior project per store location then you can make some assumptions and simply do a VLOOKUP

=VLOOKUP(ID@row,{INSIDEPRIORITY},2,FALSE)

This would look up the inside priority from the Inside priority table {with the match}. and return the Priority number assuming it is in the second column.

You do not need to match the project numbers unless you start getting multiple projects per location.

Feel free to reach out as I have 16 years of running national programmes and project rollouts..

Nancy Heater

Hi @Brent Wilson -

Yes, we can have multiple projects running for a single location, which is why we use the Project Number / Synth columns to narrow down the focus in our searches. Thanks!

Kelly Moore

Hey Nancy

I added the @cell= to your row criteria in your [Project Number] Index Collect. See Sheet E.

=INDEX(COLLECT({Test Sheet A Range 1}, {Test Sheet A Range 2}, @cell = STORE@row, {Test Sheet A Range 3}, @cell = INSIDE@row), 1)

It seems to do the trick. I can see the corresponding Priorities being collected in Sheet A against these Sheet E projects

I left Sheet F for you to test.

Let me know.

Kelly

Nancy Heater

Thanks so much @Kelly Moore !!!