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.
