CONTAINS version of INDEX, MATCH?

CONTAINS version of INDEX, MATCH?


The following formula works for me when I am trying to get the MN Description for a row when MN Ref # has only one Track ID.

=IF([Site]@row = "MN", INDEX({MN Description}, MATCH([Track ID]@row, {MN Ref #}, 0)))

My problem is that sometimes the MN Ref # will contain multiple Track IDs.

I am trying to update the formula so that it works if MN Ref # contains the Track ID I am looking for but keep getting errors and am stuck.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 06/10/21

    Try:

    =IF([Site]@row = "MN", INDEX({MN Description}, MATCH(HAS(@cell, [Track ID]@row), {MN Ref #}, 0)))

    Work or error?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • This returns "NO MATCH". I think it is because I am searching for one Track ID in the column MN Ref # where each cell may contain multiple Track ID (each separated with a space). The HAS function seems to be looking for an exact match?

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi Christine,

    That will do it. HAS works with multi-select drop downs which is what I assumed you were using. Will CONTAINS work:

    =IF([Site]@row = "MN", INDEX({MN Description}, MATCH(CONTAINS(@cell, [Track ID]@row), {MN Ref #}, 0)))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark,

    That gives me #INVALID OPERATION

  • I ended up changing the column I was trying to use the Contain function on from Text to Multiple Drop Down. This allowed me to do the following which worked.

    =IF([Site]@row = "MN", JOIN(COLLECT({MN Description}, {MN Ref #}, CONTAINS([Track ID]@row, @cell))))

    Thanks for all your help Mark!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Glad you found a solution. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!