Insert "Or" Statement in a INDEX(COLLECT), CONTAINS

BJIloveSmartSheet

Hi Team,

I need to insert an OR statement into the following formula. I would like to modify the formula to find one of two values, whichever it finds, populate. The current statement:

=INDEX(COLLECT({Interface Raw Data Range 1}, {Interface Raw Data Range 1}, CONTAINS([Interface Name]@row, @cell)), 1) - ***This formula works just fine, but doesn't include the "OR"***

If the formula finds either of the values "SAP AP Export notificationT17" or "SAP AP Export notificationT18", in the sheet "Interface Raw Data Range 1" it should populate the cell with the value it finds.

My attempt at this is below, however, that's resulting in #UNPARSEABLE. What could I be missing? Thanks!!

=INDEX(COLLECT({Interface Raw Data Range 1}, {Interface Raw Data Range 1}, IF(OR(CONTAINS("SAP AP Export notificationT17"@row, @cell)), 1),(CONTAINS(["SAP AP Export notificationT18"]@row, @cell)), 1)))

Genevieve P.

Hi @BJIloveSmartSheet

What I would do in this instance is wrap an IFERROR around your current formula. Then if your current formula errors (because there is no matching value), have it look for your second value, like so:

=IFERROR(INDEX(COLLECT({Interface Raw Data Range 1}, {Interface Raw Data Range 1}, CONTAINS([Interface Name]@row, @cell)), 1), INDEX(COLLECT({Interface Raw Data Range 1}, {Interface Raw Data Range 1}, CONTAINS("SAP AP Export notificationT18", @cell)), 1))

Let me know if that worked for you!

Cheers,

Genevieve

Jeff Reisman

@BJIloveSmartSheet

You don't need the IFs or CONTAINS' in there. Let's take those out and fix the OR syntax:

=INDEX(COLLECT({Interface Raw Data Range 1}, {Interface Raw Data Range 1}, OR(@cell = "SAP AP Export notificationT17", @cell = "SAP AP Export notificationT18"), 1)

This should work, however if your Interface Name column is multi-select and some cells contain multiple values, we'll need to incorporate the HAS function.

BJIloveSmartSheet

Thanks, It is giving me a #INCORRECT ARGUMENT.

I have attached a few screenshots that might better explain. Apologies, I am still learning Smartsheet (and coding :-)) Also, to add a bit more context, I am unable to do a "contain" to look for any value that contains SAP AP Export, because I have multiple interfaces that are unfortunately named the same (I tweaked the name with PowerAutomate), therefore I need to look for the specific value that includes 17 or 18. I hope that makes sense. :-)

APExport.png

thank you so much.

Genevieve P.

Hi @BJIloveSmartSheet

What I would do in this instance is wrap an IFERROR around your current formula. Then if your current formula errors (because there is no matching value), have it look for your second value, like so:

=IFERROR(INDEX(COLLECT({Interface Raw Data Range 1}, {Interface Raw Data Range 1}, CONTAINS([Interface Name]@row, @cell)), 1), INDEX(COLLECT({Interface Raw Data Range 1}, {Interface Raw Data Range 1}, CONTAINS("SAP AP Export notificationT18", @cell)), 1))

Let me know if that worked for you!

Cheers,

Genevieve

Jeff Reisman

@BJIloveSmartSheet Could you share the exact formula you're using that's getting the #INCORRECT ARGUMENT SET error - preferably as a screenshot from your sheet so we get Smartsheet's automatic color coding?

That will help identify the cause of the error. I suspect either #2 or #3.

#INCORRECT ARGUMENT SET

Cause

This error is presented under the following circumstances:

1. For functions that take two ranges: The range sizes don’t match for the function.
2. The function is missing an argument.
3. There is an extra function in the argument.

BJIloveSmartSheet

Thanks again! This formula seems to be working. I will test it out in the next few days. Thanks so much!

=INDEX(COLLECT=IFERROR(INDEX(COLLECT({Interface Raw Data Range 1}, {Interface Raw Data Range 1}, CONTAINS([Interface Name]@row, @cell)), 1), INDEX(COLLECT({Interface Raw Data Range 1}, {Interface Raw Data Range 1}, CONTAINS("SAP AP Export notificationT18", @cell)), 1))