Excel:当名称是一列中较长字符串的一部分时，查找列之间的匹配名称 [英] Excel: finding matching names between columns when names are part of longer string in one column

问题描述

我有一列/列表填满了预约信息(A 列)，还有另一个单独的临床医生姓名列/列表(C 列).我有兴趣将 A 列简化为临床医生姓名(与 C 列匹配).有没有一种方法/方法可以用来在 A 列和 C 列中查找匹配项，然后在 B 列中列出它们?

I have one column/list filled with appointment information (column A), and another separate column/list of clinician names ( column C). I am interested in simplifying column A down to just a clinician name(its match from column C). Is there a method/approach which could be used to find matches in column A and C, and then list them in column B?

A 列大约有 1,100 行，C 列大约有 200 行(名称).

Column A has about 1,100 rows, Column C about 200 rows(names).

提前感谢您的时间和考虑！

Thank you in advance for your time and consideration!

A 列预约信息08/06/2018 @ 10:00 AM(240 分钟)AA MH/PHD 测试 CLC=IP估计病人CID/PID:08/06/2018(等待:0 天)预定于:6/13/2018作者:苏西筷子评论:F107没有出现次数:4英里到诊所:NA

Column A Appointment Info 08/06/2018 @ 10:00 AM (240 min) AA MH/PHD Testing CLC=IP Est Patient CID/PID:08/06/2018 (Wait: 0 days) Scheduled on: 6/13/2018 By: Suzie Chapstick Comments: F107 No Show Count:4 Miles to Clinic: NA

08/08/2018 @ 12:00 PM(120 分钟)AA MHC/CHOL-哈普曼 EVAL新病人CID/PID:07/03/2018(等待:36 天)预定于:7/3/2018作者:莱格，斯坦利评论:根据 MHCNoShow 计数:7英里到诊所:NA

08/08/2018 @ 12:00 PM (120 min) AA MHC/CHOL-Harpman EVAL New Patient CID/PID:07/03/2018 (Wait:36 Days) Scheduled on: 7/3/2018 By: Legg, Stanley Comments: Per MHC NoShow Count: 7 Miles to Clinic: NA

08/06/2018 @ 09:00 AM(180 分钟)AA MHC/PSY-Stinger 进气新病人CID/PID:6/7/2018(等待:60 天)预定于:7/6/2018作者:芬克拜纳，玛丽亚评论:根据 MHCNoShowCount: 3英里到诊所:16

08/06/2018 @ 09:00 AM (180 min) AA MHC/PSY-Stinger Intake New Patient CID/PID: 6/7/2018 (Wait:60 days) Scheduled on: 7/6/2018 By: Finkbeiner, Maria Comments: Per MHC NoShowCount: 3 Miles to Clinic: 16

C 列棕色的邓肯芬利竖琴手毒刺

Column C Brown Duncan Finley Harpman Stinger

推荐答案

你可以试试这个:

=IFERROR(INDEX(Clinicians,MATCH(1,MATCH("*"&Clinicians&"*",A1:A10,0),0)),"not found")

这是我的结果:

我给了临床医生一个范围以提高可读性，Col A 也可以这样做.我还建议临床医生完全在一个单独的标签上.

I gave the Clinicians a range to help the readablity, the same could be done with Col A. I'd also suggest the clinicians be on a separate tab altogether.

第一个未找到"在临床医生列表中没有命中"，第二个和第三个是相对的空白单元格.

The First "not found" has no "hits" in the clinicians list, the second and third are opposite blank cells.

希望有帮助

祝你好运

这篇关于Excel:当名称是一列中较长字符串的一部分时，查找列之间的匹配名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！