HAS exact match within multiselect - Numbered Values

Rob W.

Scenario:

Trying to identify a match if a value shows up in a multiselect from another sheet.

Approach:

I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more appropriate than (contains), and this should be solving my problem, but instead of false positives, I now get false negatives. I split this out as a standalone formula to get this fixed before I nest this into the index(collect) function.

Problem:

The values I'm looking for are numbered, and will inevitably include partial matches (if the source multiselect includes 1000, this also will return a match if I look for 0, 1, 10, 100, 00, etc.). Trying to isolate exact matches since 1, 10, 100, 1000 are all totally unique IDs in the source table and should be returning different lookup values through the index(collect) function.

Unclear how to get HAS to pick up an exact match when there will be partials here. Thought about using some kind of =right / =mid / =find to index off of a " " between the values in the string that returns from the multiselect, but this isn't going to be consistent either since some cells will contain only one value, and I never know if the match I'm trying to make is the first, last, or in the middle of the multiselect string.

Appreciate any suggestions on how to navigate this.

Shanky Paul

Hi,

Instead of applying the formula to "Multiselect Text String" row, did you tried with "Multiselect Values" row?

=IF(HAS([Multiselect Values]@row, [Component ID]@row), "MATCH", "NO MATCH")

Thank you,

Shanky Paul

@Rob W.

Try the below formula:

=JOIN(COLLECT([Record ID]:[Record ID], [Component ID]:[Component ID], [Lookup ID]@row), " ")

Thank you

Shanky Paul

Hi,

Instead of applying the formula to "Multiselect Text String" row, did you tried with "Multiselect Values" row?

=IF(HAS([Multiselect Values]@row, [Component ID]@row), "MATCH", "NO MATCH")

Thank you,

Rob W.

Thanks Paul,

This worked to eliminate my false negatives - (I'm always surprised how Smartsheet treats different data types, I had expected the back end to see the same string whether the front end formatting shows this as a list of multiselect values or as text) - but I'm now facing a different issue:

My goal is to return multiple Record IDs based on a Component ID that may show up multiple times in the table. I set this up locally to validate but will ultimately set this up as an external sheet reference.

New Problem:

My formula is only returning one of multiple matches. I know if I set this up as a COUNTIF(), there would be multiple hits, but unclear how to collect and concatenate / append the text values from multiple matches.

In the below example, all 5 lookup ID rows should return two RFO numbers in the 'Records Per Component' column.

=INDEX(COLLECT([Record ID]:[Record ID], [Component ID]:[Component ID], [Lookup ID]@row), 1)

Shanky Paul

@Rob W.

Try the below formula:

=JOIN(COLLECT([Record ID]:[Record ID], [Component ID]:[Component ID], [Lookup ID]@row), " ")

Thank you

Rob W.

@Shanky Paul,

Appreciate the follow-up on this, the JOIN function did the trick. I then replaced the local column references with cross-sheet references and this is working as intended.

Thanks!