# Matching multiple comma separated values in a cell

Based on values in first two columns I need to populate third column (marked with yellow).

## Is something like this possible without using VB?

• I can suggest you UDF (VBA) will work as a Excel Formula without any problem, please confirm whether you need this or not? – Rajesh S Jul 4 '18 at 16:10
• Are there always 4 possibilities with 2 winning teams or does the numbers vary? – Kevin Anthony Oppegaard Rose Jul 4 '18 at 16:26
• It is always 4 participants, 2 winners and 2 losers, and I need it without VBA – user3760419 Jul 4 '18 at 17:03
• @user3760419, the solution what I was suppose to suggest can work with any combination 4 +2 or 5+3 or with words and number even and it's very simple to use ! – Rajesh S Jul 5 '18 at 6:51
• @RajeshS, unfortunately VB is not an option – user3760419 Jul 5 '18 at 8:11

EDIT:

If there are always four teams with two winners and two losers, there is a relatively simpler solution:

``````=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1&",",LEFT(B1,FIND(",",B1)),""),MID(B1,FIND(",",B1)+1,LEN(B1))&",","")&",",",,","")
``````

Yes, this is possible with just an array formula. It's not exactly simple, though.

The following is a general solution that works with any number of participants and winning teams: Array enter (Ctrl+Shift+Enter) the following formula in `C2` and copy-paste/fill-down into the rest of the table column (don't forget to remove the `{` and `}`):

``````{=
TEXTJOIN(
",",
TRUE,
IF(
MMULT(
--(
TRIM(MID(SUBSTITUTE(A2&",",",",REPT(" ",LEN(A2)+1)),(ROW(INDEX(C:C,1):INDEX(C:C,(LEN(A2)+1)/2))-1)*(LEN(A2)+1)+1,LEN(A2)+1))
=TRANSPOSE(TRIM(MID(SUBSTITUTE(B2&",",",",REPT(" ",LEN(B2)+1)),(ROW(INDEX(D:D,1):INDEX(D:D,(LEN(B2)+1)/2))-1)*(LEN(B2)+1)+1,LEN(B2)+1)))
),
ROW(INDEX(C:C,1):INDEX(C:C,1+LEN(B2)-LEN(SUBSTITUTE(B2,",",""))))
),
"",
TRIM(MID(SUBSTITUTE(A2&",",",",REPT(" ",LEN(A2)+1)),(ROW(INDEX(C:C,1):INDEX(C:C,(LEN(A2)+1)/2))-1)*(LEN(A2)+1)+1,LEN(A2)+1))
)
)}
``````

Note that `TEXTJOIN()` is only available in Excel 2016. In earlier versions of Excel, a poly-fill UDF is required. (See this post for a basic one.)

Explanation:

coming soon

For now, though, hopefully the following partly evaluated version of the formula in `C2` might help:

``````{=
TEXTJOIN(
",",
TRUE,
IF(
MMULT(
--(
{"A";"B";"C";"D"}
={"A","C"}
),
{1;2}
),
"",
{"A";"B";"C";"D"}
)
)}
``````
• The explanation will have to wait until after I get some sleep ;-) – robinCTS Jul 4 '18 at 16:39
• Wow, tnx Robin! I will check if 2016 will be avaliable. As I always have 4 teams with 2w and 2l could the solution be simplified so that it can run without text join and without VB? – user3760419 Jul 4 '18 at 17:10
• @user3760419 You just caught me ;-) Yes it can be simplified so it doesn't use `TEXTJOIN()`. I don't understand what you mean with "without VB" as there is no VBA unless you don't have Excel 2016. Unless that's what you mean :-) PS I've updated the answer with the simpler non-general solution. – robinCTS Jul 4 '18 at 17:27
• Perfect, now I have both great generic solution (in case 2016 is available) or a specific ones that will support all versions. Tnx !!! – user3760419 Jul 5 '18 at 8:10