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