Skip to main content

Is there a formula that chooses an item from a list /arrayand displays what number in the list it was?


For example, I imported data from Zapier that says “No,No,Yes,No,No”


Is there a function that tells me that Yes is the third option? Or something in Zapier? Thanks!

If your goal is to find the index of the “Yes” among a sea of “No” items, here’s how you can do that.


First, it partly depends on whether you have a single string that includes commas, or if you have an array and the commas are only in the field display. If you have a string, you’ll need to first remove all commas using SUBSTITUTE. I’m using a field named {Data} for this, but replace this with the name of the field that has the data from Zapier:


SUBSTITUTE(Data, ", ", "")

If you have an array, you would instead do this:


ARRAYJOIN(Data, "")

In either case, you’ll end up with a comma-less mashup of all results:


NoNoYesNoNo

Surrounding the above function (whichever is appropriate for your situation), add a nested pair of SUBSTITUTE functions. One will replace each Yes with a single character (I’ll use Y), the other will replace each No with a different character (I’ll use N). Using the substitute option above, we now have this:


SUBSTITUTE(

    SUBSTITUTE(

        SUBSTITUTE(Data, ", ", ""),

        "Yes",

        "Y"

    ),

    "No",

    "N"

)

Finally, wrap a FIND around that to search for the lone Y:


FIND(

    "Y",

    SUBSTITUTE(

        SUBSTITUTE(

            SUBSTITUTE(Data, ", ", ""),

            "Yes",

            "Y"

        ),

        "No",

        "N"

    )

)

…which will return a 3.

Reply


Terms & Conditions