Find the location of matching cell value (single number) in another column that contains multiple numbers separated by commas

[FishSea]

Hi,

I've been stuck on this one for a bit now. I have two sheets with a table in each, examples below:

Sheet name "tiers", table name "orig":

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: left"]cid[/TD]
[TD="width: 64, align: left"]tier[/TD]
[/TR]
[TR]
[TD="align: right"]92944741[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]40695415[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]94022261[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]72356125[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]99855973[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]7216489[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]30203042[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]63537001[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]6606033[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]18674819[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]16886282[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]54573501[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]80292130[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]24793180[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]33398063[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]85914584[/TD]
[TD="align: right"]14[/TD]
[/TR]
</tbody>[/TABLE]

Sheet name "customers", table name "contacts":

[TABLE="width: 304"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: left"]Email Address[/TD]
[TD="align: left"]cid list[/TD]
[/TR]
[TR]
[TD="align: left"]OOEK@fake.com[/TD]
[TD="align: left"]17644399,40695415,0[/TD]
[/TR]
[TR]
[TD="align: left"]XMIQ@fake.com[/TD]
[TD="align: left"]94022261,41276315,0[/TD]
[/TR]
[TR]
[TD="align: left"]RMEH@fake.com[/TD]
[TD="align: left"]99855973,69744518,0[/TD]
[/TR]
[TR]
[TD="align: left"]JCGZ@fake.com[/TD]
[TD="align: left"]30203042,27393148,0[/TD]
[/TR]
[TR]
[TD="align: left"]VBPH@fake.com[/TD]
[TD="align: left"]54573501,347474742,81366419,0[/TD]
[/TR]
[TR]
[TD="align: left"]XQHK@fake.com[/TD]
[TD="align: left"]50811900,16886282,0[/TD]
[/TR]
[TR]
[TD="align: left"]AUKJ@fake.com[/TD]
[TD="align: left"]85914584,20671229,0[/TD]
[/TR]
[TR]
[TD="align: left"]PBWS@fake.com[/TD]
[TD="align: left"]92944741,31197542,0[/TD]
[/TR]
[TR]
[TD="align: left"]NBFI@fake.com[/TD]
[TD="align: left"]22992367,6606033,0[/TD]
[/TR]
[TR]
[TD="align: left"]UKWC@fake.com[/TD]
[TD="align: left"]33398063,62629360,9999999,0[/TD]
[/TR]
[TR]
[TD="align: left"]NTUE@fake.com[/TD]
[TD="align: left"]72356125,223344,60758952,0[/TD]
[/TR]
[TR]
[TD="align: left"]ITTE@fake.com[/TD]
[TD="align: left"]18674819,24194445,0[/TD]
[/TR]
[TR]
[TD="align: left"]EYMG@fake.com[/TD]
[TD="align: left"]7216489,52159608,3848484,0[/TD]
[/TR]
[TR]
[TD="align: left"]NXJB@fake.com[/TD]
[TD="align: left"]24793180,25590276,0[/TD]
[/TR]
[TR]
[TD="align: left"]IVDR@fake.com[/TD]
[TD="align: left"]63537001,74712916,0[/TD]
[/TR]
[TR]
[TD="align: left"]SYTZ@fake.com[/TD]
[TD="align: left"]30346954,80292130,0[/TD]
[/TR]
</tbody>[/TABLE]

What I'm trying to do is, in a 3rd column in the "orig" table, show the email address for the corresponding cid, by finding the matching cid value in the "contacts" table 'cid list'. I'm open to both formulas and/or VBA, however I'm less keen on , because there's not a set amount of comma separated numbers that will appear in the cid list. It could be 2, 3, or more, the numbers could have different amounts of digits, and this will be different for different contacts/customers. Also, I'd prefer to do this in the 'orig' table, as it is already organized by 'tier', and I'm trying to make this an automated process as there is new data for this every day.

I've been able to find a formula that finds if the cid is located in the cid list, but not where the cid is located in that list (and by extension, haven't been able to return the corresponding email). When testing this formula, I was using it in the customers sheet/contacts table (reverse of where I want, as described above, but it helped with testing the formula) - 'A' refers to the cid column in the orig table, and 'B' to the cid list column in the contacts table:

=IF(SUM(IFERROR(SEARCH(cid!A:A,B1),0))>0, "FOUND", "NOT FOUND")

Any help would be appreciated. Please let me know if any clarification would help - this is my first time posting a question here.

 

[Aladin Akyurek]

Thanks Aladin! I tried that Lookup formula, it also works great for my example chart. In my actual data, the number of contacts changes each day, and while I could most likely change the range in the lookup formula with a little work, I've already implemented the macro mumps provided. However, I'm sure that formula could/will come in handy for me in the future if a situation arises where the macro is less efficient. Either way, good to know this can also be done without macro use!

You are welcome. thanks for the update.