Formula to use cell with comma separated values as Lookup value

Cwillson

New Member
Joined
Oct 1, 2015
Messages
36
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,771
Office Version
  1. 365
Platform
  1. Windows
Can you post some sample data, so that we can see what your data looks like.
 

Cwillson

New Member
Joined
Oct 1, 2015
Messages
36
Office Version
  1. 2019
Platform
  1. Windows
ABC (Result)
11,5,7,1011,5
27,2,3,933
31,5,6,951,5,9
49

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

Board Regular
Joined
Jul 30, 2020
Messages
203
Office Version
  1. 365
Platform
  1. Windows
Shouldn't the result in Row 2 be "3,9"? Assuming that a typo, please see below.

Test.xlsx
ABCD
1ABC (Result)
21,5,7,1011,51,5
37,2,3,9333,9
41,5,6,951,5,91,5,9
59 
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=TEXTJOIN(",",1,IFERROR($B$2:$B$5/ISNUMBER(SEARCH($B$2:$B$5,A2)),""))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,587
Office Version
  1. 365
Platform
  1. Windows
@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
ABC
1ABC (Result)
21,5,7,1011,5
37,2,3,933,9
41,5,6,951,5,9
59
Sheet1
Cell Formulas
RangeFormula
C2:C4C2=TEXTJOIN(",",1,IF(ISNUMBER(FIND(","&B$2:B$5&",",","&A2&",")),B$2:B$5,""))
 

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
203
Office Version
  1. 365
Platform
  1. Windows
@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
ABC
1ABC (Result)
21,5,7,1011,5
37,2,3,933,9
41,5,6,951,5,9
59
Sheet1
Cell Formulas
RangeFormula
C2:C4C2=TEXTJOIN(",",1,IF(ISNUMBER(FIND(","&B$2:B$5&",",","&A2&",")),B$2:B$5,""))
You are right Peter, I totally missed that :oops:
 

Forum statistics

Threads
1,176,266
Messages
5,902,244
Members
434,954
Latest member
kchallr00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top