Converting a list of names

[Vic14]

Hi all,
I have a problem that my limited excel experience is unable to solve and I would be grateful for your help.
I have a long list of names. I know that some of the names are nurses and some are doctors and some are care assistants. The names will repeat in the column because there is also a separate column with a unique patient identifier. In the column next to the names I want to be able to display whether they are a doctor, nurse or care assistant.
I have attached a file for illustrative purposes. The list of names is in Column B. The job titles are in Column E and F. There will be lots of names in the final lists.

I have tried using an index, match, isnumber, search formula but I don't know how to enter the names automatically rather than having to enter them as " text ". This isn't practical given the number of names.

It feels like it should be really easy but I have had a complete block.

Many thanks

 

[MSC Bobs]

I think this is what you were trying to do.

 

[Ron Coderre]

First...to work "with" Excel (instead of against it)
I transformed your name/position lists into a 2-column table (named: tblNameXRef)

Name             Position
John Brown       Doctor
Kim Seale        Doctor
Duncan Wise      Doctor
Rachel Plaice    Doctor
Jane Salt        Nurse
Alison Parks     Nurse
Sarah Mead       Nurse
Michael Anthony  Nurse

Second, I converted your data to an Excel Table named tblPatStaff

Patient number  Name
123             John Brown
124             Jane Salt
132             Kim Seale
293             Duncan Wise
182             Alison Parks
102             Sarah Mead
387             Rachel Plaice
223             Michael Anthony
923             Duncan Wise
432             Alison Parks

Third, I added a Position column to the tblPatStaff table
and entered this formula that assigns positions to each staff name:

=IFERROR(INDEX(tblNameXRef[Position],MATCH([@Name],tblNameXRef[Name],0)),"Unknown")

These are the results:

Patient number  Name            Position
123             John Brown      Doctor
124             Jane Salt       Nurse
132             Kim Seale       Doctor
293             Duncan Wise     Doctor
182             Alison Parks    Nurse
102             Sarah Mead      Nurse
387             Rachel Plaice   Doctor
223             Michael Anthony Nurse
923             Duncan Wise     Doctor
432             Alison Parks    Nurse

Is that something you can work with?

 

[bosco_yip]

Or......................



In C3, copied down :

=INDEX(E$3:F$3,SUMPRODUCT((E$4:F$7=B3)*COLUMN(A1:B1)))

Regards
Bosco

 

[Vic14]

Fantastic. Thank you for the help. I wasn't aware of the 'table' function part of excel and have clearly been working against excel.

I am very grateful.