Index Match, how to recognize multiple values?



I have an issue with my index match formula. I want to locate the facility for a serial number. This works perfectly when there is only one value inside. However to reduce the amount of columns and options on the corresponding form, I changed it to one column that has multiple values. The formula is having difficulty seeing that the serial number exists in that column because there is two in the cell. How do i fix this?

Working formula below, ( when it returns to stock it goes positive, signifying that it is back at the home warehouse named "pes")

=IF(Available@row > 0, "PES", INDEX(Facility:Facility, MATCH(6633112443, [Microscope: 1]:[Microscope: 1], 0)))

When i change the column to allow multiple values in one cell however, it no longer recognizes that serial number is there.

Any help is appreciated!


Best Answer


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Marcus Halvorson

    Try this

    =IF(Available@row > 0, "PES", JOIN(INDEX(COLLECT(Facility:Facility, [Microscope: 1]:[Microscope: 1], HAS(@cell,"6633112443")), 1)))

    Will this work for you?


  • Marcus Halvorson

    @Kelly Moore

    I am not sure how that works, but it seems to work perfect!! I can finally progress the sheet and form. This has been stumping me since Wednesday. I am newer to smartsheets and do not quite understand how the order or parameters should be filled when using more than one function at a time.

    Amazing :) This will be a good week


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!