VLookup with Comma Interval

[renjie0315]

Hi all, 

I would like to use VLookup to abstract the phone number and email based on the name given in cell B3 and subsequent column. However, in between the name, there is a comma, may I know is there any method to achieve this?

[#2]

=INDEX(TEXTSPLIT(TBL[Name],","),1,1). You might need =TRIM too.

[#3]

@renjie0315 Or perhaps Power Query. Just a quick-and dirty-solution in the attached file.

[#4]

Hi @Riny_van_Eekelen,

I have downloaded your excel file. However, I can't find any formula or method that you have done, it's a plain file basically. Would you mind to show me where can I locate it?

[#5]

@renjie0315 I've used Power Query (PQ). If you are not familiar with PQ, it's not so easy to just start using it. You could start learning from the site in the link below.

It clearly explains all the basics and more advanced features of Power Query.

https://exceloffthegrid.com/power-query-introduction/ 

[#6]

Hi @ecovonrein,

May I know what is TBL[Name]? It shows there's a problem with this formula. May I know how should I solve this?

[#7]

Hi @ecovonrein,

I presume TBL stands for Table. This is the error that I faced when I select the table.

[#8]

or perhaps online tool http://e.anyoupin.cn/ceshi/jstest/pull_up_demo.php?s=asic_split_data_from_multi_columns video https://www.douyin.com/video/7202025919966383360

[#9]

I dunno the naming conventions of your spreadsheet. TBL was just a placeholder for whatever name you use. From the screenshot it appears your TBL is called "Table1_2". Except that while it looks like a "Table", the #NAME suggests that it isn't actually an Excel table because the structured reference "[Name]" does not appear to be recognized. So replace that structured reference with your hard coordinates, eg =INDEX(TEXTSPLIT(B3,","),1,1).

[#10]

@renjie0315 
Perhaps with M365...
=SORT(UNIQUE(WRAPROWS(TOCOL(WRAPCOLS(TOCOL(TEXTSPLIT(TEXTJOIN("♡",1,B3:D4),{"♡",", "})),2),,FALSE),3)))