Formula to use cell with comma separated values as Lookup value

[Cwillson]

Hey peeps,

I have a spreadsheet with a range in column (A) containing comma separated values in each cell.

I also have another range in column (B), consisting of cells with just a single value in each.

I'm looking for a formula which will confirm which of the comma separated values in column A can be found in column B. There may of course be more than one, so ideally I'd like a comma separated cell with the matching values.

If this last bit is super complex using formula (I can't run VB on work laptop - sigh) then it would be fine to simply return a "Y" if any of the comma separated values in column A can be found in column B!

Many thanks in advance!

C

 

[Fluff]

Can you post some sample data, so that we can see what your data looks like.

 

[Cwillson]

	A	B	C (Result)
1	1,5,7,10	1	1,5
2	7,2,3,9	3	3
3	1,5,6,9	5	1,5,9
4		9

Hi Fluff,

It's something like this. If comma separated values in A1 are found in B:B the ideally populate C1 with comma separated matches.

 

[Habtest]

Shouldn't the result in Row 2 be "3,9"? Assuming that a typo, please see below.

Test.xlsx
	A	B	C	D
1	A	B	C (Result)
2	1,5,7,10	1	1,5	1,5
3	7,2,3,9	3	3	3,9
4	1,5,6,9	5	1,5,9	1,5,9
5		9
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=TEXTJOIN(",",1,IFERROR($B$2:$B$5/ISNUMBER(SEARCH($B$2:$B$5,A2)),""))

 

[Peter_SSs]

@Cwillson
As mentioned by Habtest, hopefully the result you show for 7,2,3,9 was a mistake since both 3 and 9 appear in column B?


@Habtest
That could return some incorrect results. For example, try changing cell A2 in your sample to 2,5,7,10

The following amendment should avoid that problem.

Cwillson.xlsm
	A	B	C
1	A	B	C (Result)
2	1,5,7,10	1	1,5
3	7,2,3,9	3	3,9
4	1,5,6,9	5	1,5,9
5		9
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=TEXTJOIN(",",1,IF(ISNUMBER(FIND(","&B$2:B$5&",",","&A2&",")),B$2:B$5,""))

 

[Habtest]

@Habtest
That could return some incorrect results. For example, try changing cell A2 in your sample to 2,5,7,10

The following amendment should avoid that problem.

Cwillson.xlsm
	A	B	C
1	A	B	C (Result)
2	1,5,7,10	1	1,5
3	7,2,3,9	3	3,9
4	1,5,6,9	5	1,5,9
5		9
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=TEXTJOIN(",",1,IF(ISNUMBER(FIND(","&B$2:B$5&",",","&A2&",")),B$2:B$5,""))

You are right Peter, I totally missed that