Here is my problem, I have a table with two columns: product references and corresponding notice ids:
| A | B | C | D |
---------------------------------------
1| Product | Notice | | |
2| p1 | n1 | | |
3| p2 | n2 | | |
4| p3 | n3 | | |
5| | | | |
6| | | p1, p3 | =... |
(edit: in my real life application, columns 'product references' and 'notice ids' are not alongside but separated by other columns)
In another cell (e.g. C6), I have a comma separated list of product references, let's say p1, p3 and I need a formula to output the corresponding notice ids, i.e. n1, n3 in this case, in cell D6.
Important: For different reasons, I cannot use VBA, I need a standard excel array formula.
Here is what I can do at the moment:
with the
FILTERXMLfunction, I can split the comma-separated list into an array:FILTERXML("<t><s>" & SUBSTITUTE(C6, ", ", "</s><s>") & "</s></t>", "//s")with the
TEXTJOINfunction, I can merge an array into a string.I can extract a single match with a combination of
INDEXandMATCHfunctions, e.g.:
=IF(ISERROR(MATCH("p3"; A:A; 0)); "not found"; INDEX(B:B; MATCH("p3"; A:A; 0)))
(which is not useful for me, since again the references in column A are unique)
(By the way, I don't know if there is a better way to handle error raised by MATCH when no match is found)
- I can extract and join elements of column B corresponding to multiple matches to a single reference in column A with (array formula activated with Ctrl+Shift+Enter):
{=TEXTJOIN(", "; TRUE; IF(A:A="p2"; B:B; ""))}
(which is not useful for me, since again the references in column A are unique)
In summary: I can find and merge multiple matches to a single reference, but I cannot find and merge single unique match to multiple references (what I want to do).
Failed attempts
I tried to mix the previous formulae in different ways to get what I want, but all failed with an error.
- Combining 1, 2 and 4 (using
ORon boolean array of matches):
{=TEXTJOIN(", "; TRUE; IF(OR(A:A=FILTERXML("<t><s>" & SUBSTITUTE(C6, ", ", "</s><s>") & "</s></t>", "//s")); B:B; ""))}
or (using SUM on boolean array of matches):
{=TEXTJOIN(", "; TRUE; IF(SUM(A:A=FILTERXML("<t><s>" & SUBSTITUTE(C6, ", ", "</s><s>") & "</s></t>", "//s")); B:B; ""))}
Here, I am not sure how to handle the different arrays that are considered in the IF (column A and list of references given by FILTERXML).
- Combining 1, 2 and 3:
{=TEXTJOIN(", "; TRUE; INDEX(B:B; MATCH(FILTERXML("<t><s>" & SUBSTITUTE(C6, ", ", "</s><s>") & "</s></t>", "//s"); A:A; 0)))}
Here, I am not sure how to handle (i) again the different arrays that are considered (column A and list of references given by FILTERXML), (ii) the error raised by MATCH when no match is found, (iii) the array references passed to INDEX function.
