Index Match, how to recognize multiple values?

Marcus Halvorson

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,

Kelly Moore

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

Kelly Moore

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

@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