Forum Discussion
Comparing two columns with comma separated string values (match)
Hi,
I currently have two columns of reference data. Each cell contains comma separated values.
My aim is to find a match between any of the patent numbers in column A within the corresponding row in column B. E.g. in row 2, there is a match between value in column 1 (CN...385A) and column 2).
Ideally the output in column C would be "yes" or "match".
Ideally want to keep cells formatted as they are, with delimiters between patent numbers.
many thanks
- Hi,
Just a follow-up question to this.
I was wondering if the output in cell C2 instead of showing Match, could in fact show the matching citation itself.
E.g. in row 2, there is a match between value in column 1 (CN117860385A) and column 2. In column C, the output would be the matching citation: CN117860385A as opposed to "Match".
many many thanksHow about
=LET(a, TEXTSPLIT(A2, "|"), b, TEXTSPLIT(B2, "|"), IFERROR(FILTER(a, ISNUMBER(XMATCH(a, b))), ""))
