Index Match, how to recognize multiple values?

Options

Hello,

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!

Thanks,

Best Answer

Answers

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

    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?

    Kelly

  • Marcus Halvorson
    Options

    @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


    Thanks

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!