I would filter based on REGEXMATCH. The regular expression would be the join of one of two rows. Suppose the names are in rows 4 and 5, and columns E through J. I'm using row 5 to make a regular expression, joining the names with "or" symbol |:
="^("&JOIN("|", FILTER(E5:J5, LEN(E5:J5)))&")$"
This returns "Lars|Anairis|Emil|Johan|Leela". Note I'm filtering out any blank cells to avoid getting || in regular expression, which would match anything. Also, the ^ and $ symbols ensure that only the entire string will match.
Then filter row 4 by the above regular expression (replaced with ... for readability):
=JOIN(", ", FILTER(E4:J4, REGEXMATCH(E4:J4, ... )))
This returns "Leela, Anairis, Lars", which is the desired result.
When put together, the whole formula is somewhat scary:
=JOIN(", ", FILTER(E4:J4, REGEXMATCH(E4:J4, "^("&JOIN("|", FILTER(E5:J5, LEN(E5:J5)))&")$")))
Since this is more a regular expression logic update, I can contribute:
Try:
'^(mimz|mi[n-z]|m[j-z]|[n-r]|sa[a-m]|san[a-d]).*'
Just keep in mind that the range above needs to differ by the first character. If not, then pull the common characters out. For mimz-mitz:
'^mi(mz|[n-s]|t[a-z]).*'
Not saying there isn't an easier way -- this just helps you update this particular way in the future.
Best Answer
The formula works fine.
The issue is that blank cells are counted first.
You can find your rows with data in the end of the document.
Exclude blank cells to correct the view you by using the following formula.