Excel: finding matching names between columns when

问题:

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?

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

Thank you in advance for your time and consideration!

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 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 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

Column C Brown Duncan Finley Harpman Stinger

回答1:

You can try this:

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

Here are my results:

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.

Hope that helps

Good Luck