CONTAINS version of INDEX, MATCH?

Options

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
    Options

    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.

  • christine.r.perrey93816
    Options

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

    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.

  • christine.r.perrey93816
    Options

    Mark,

    That gives me #INVALID OPERATION

  • christine.r.perrey93816
    Options

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

    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!