Choosing item from list

[Carson_Schutter]

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!

[Justin_Barrett]

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.