If / Contains combination

Rebecca Menning

Just recently, we have been experiencing automated workflow messages triggered based on formulas that are years old 😯 It appears that these messages are triggering each time a user sorts the originating sheet a different way. In an attempt to debug the issue, I found the below. Any thoughts as to why the destination Smartsheet is showing a location of 3?

Thanks

Becky Menning

Genevieve P.

Hi @Rebecca Menning

Thanks for clarifying! In this image, it looks like "1" is a criteria and not the output that you're looking for.

What I would do here is use INDEX(COLLECT instead of INDEX(MATCH, as you can then add multiple criteria into what you're looking to match on.

For example:

=INDEX(COLLECT({Date to return}, {Provider Name}, Name@row, {Location}, 1), 1)

So in your case, something like this:

=INDEX(COLLECT({BJCMG Provider Roster Range Start Date}, {BJCMG Provider Roster Range Provider Full Name}, [Provider Full Name]@row, {BJCMG Provider Roster Range Location}, 1), 1)

Note that this will need to be put in a Date type of column in order to return a Date from the source sheet. Here's more information: https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations#toc-lookup-one-cell-using-multiple-criteria

Cheers,
Genevieve

Rebecca Menning

Just a step further

When I resort the source sheet….. the correct location shows on the destination sheet

Paul Newcome

INDEX/MATCH will start at the top of the range and pull the first match. So if the sheet is sorted to where the 3 is the first match for that provider, INDEX/MATCH will output a 3. If the source data is sorted again so that the 1 is the first match for that provider, the INDEX/MATCH will output a 1.

Rebecca Menning

So what formula can I use to ensure it only pulls when practice location = 1?

Paul Newcome

You would basically need to evaluate the INDEX/MATCH with the IF statement along the lines of

=IF(INDEX/MATCH = 1, 1)

Rebecca Menning

OK … now I am getting an Invalid operation.

=IF({BJCMG Provider Roster Range Location} = "1", INDEX({BJCMG Provider Roster Range Start Date}, MATCH([Provider Full Name]@row, ({BJCMG Provider Roster Range Provider Full Name}), 0)))

Genevieve P.

Hi @Rebecca Menning

Can you explain what it is you want the formula to do?

If there are multiple matches, should it always present "1"? Is it the same numbering system in the source sheet for all locations?

Paul Newcome

@Rebecca Menning Your syntax is still off.

You have

=IF({Range} = 1, INDEX/MATCH)

Try

=IF(INDEX/MATCH = 1, 1)

Rebecca Menning

Still not having any luck. See below for additional detail on what I am trying to do. Thank you!

Genevieve P.

Hi @Rebecca Menning

Thanks for clarifying! In this image, it looks like "1" is a criteria and not the output that you're looking for.

What I would do here is use INDEX(COLLECT instead of INDEX(MATCH, as you can then add multiple criteria into what you're looking to match on.

For example:

=INDEX(COLLECT({Date to return}, {Provider Name}, Name@row, {Location}, 1), 1)

So in your case, something like this:

=INDEX(COLLECT({BJCMG Provider Roster Range Start Date}, {BJCMG Provider Roster Range Provider Full Name}, [Provider Full Name]@row, {BJCMG Provider Roster Range Location}, 1), 1)

Note that this will need to be put in a Date type of column in order to return a Date from the source sheet. Here's more information: https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations#toc-lookup-one-cell-using-multiple-criteria

Cheers,
Genevieve

Rebecca Menning

Thanks Paul and Genevieve. This is looking successful !

Becky Menning