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

Options

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

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/15/23
    Options

    @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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • BJIloveSmartSheet
    edited 02/16/23
    Options

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

    thank you so much.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @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.


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • BJIloveSmartSheet
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!