If value in list, then assign label

Kinno

New Member
Joined
Mar 29, 2019
Messages
6
Hi All,

Good morning/afternoon/evening depending on where you are in the world.

I've run into a little problem on this fine Monday morning! I feel like its a really easy one and I'm just being an idiot, or the weekend hasn't quite worn off completely I'm not sure! I've tried looking online and on the forum however I feel like my wording in the searches hasn't been producing the right results.

Basically I have a list of patients and they've been seeing two types of doctors, psychologists and psychiatrists. I want to make it so when the doctors name is entered it automatically assigns their role in the next cell. I have all the doctors names in two lists based on their role and would like to use that list the check against the name entered on the patient list.

Patient No.Doctor NameDoctor RolePsychologistPsychiatrist
1234Ollie Ollison(Psychologist)Ollie OllisonMichael Michaelson
1235Phil Philson(Psychiatrist)David DavidsonPhil Philson
1236David Davidson(Psychologist)Laura Laurenson
1237David Davidson(Psychologist)Steve Stevenson
1238Laura Laurenson(Psychologist)
1239Michael Michaelson(Psychiatrist)
1240Phil Philson(Psychiatrist)

<tbody>
</tbody>

The "Doctor Role" column is the one I'd like the formula in and the text in brackets is how it should read off if it works! As I said I don't think its actually as complicated as I'm making it for myself but Monday is Monday! Hope someone can help!
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
Why dont you have one list with two columns.
First column is name, second column is 1 or 2 indicating Psychologist or Psychiatrist (This assumes a doctor cannot be both).
Then just do a simple VLOOKUP

IF(VLOOKUP(B2,Doctors!$A$1:$B$1000,2,0)=1, "Psychologist","Psychiatrist")
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
Hey Kinno,

Try this formula:
=IF(SUMPRODUCT(--(B2=$D$2:$D$5))=1,"(Psychologist)","(Psychiatrist)")

Assuming B2 is your first Doctor Name and D2:D5 are the list of Psychologists (change the range accordingly) - input that formula in to cell C2 and drag it down the C column.

Note: The default answer to this formula will return "(Psychiatrist)" (If the Doctor Name is NOT a Psychologist) but you can change if desired.
 
Last edited:

Kinno

New Member
Joined
Mar 29, 2019
Messages
6
Thank you both for the quick replies! I tested both these solutions and they work great. However something I overlooked in my explanation is that some of the data has blanks. Is there any way of adjusting the formulas to just read nothing for a blank cell as i want to drag this down a long list and total the number of each!

For now Special-K99 reads "#N/A" when theres a blank which is something close to what I want, however I'm setting up this database for people that arent as savvy with excel and I want to avoid confusing them as much as possible, so I feel like a blank cell is less likely to intimidate them! If its not possible then I'll leave as is.

tyija as worked how i wanted but reads psychiatrist as the default value which will confuse later calculations and staff. Im not sure which of the two is most easily adjustable.

Thanks again to both regardless!
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
#N/A implies the doctor is not on in your list 9assuming you have reduced both lists to one)

This should fix it

=IFERROR(IF(VLOOKUP(B2,Doctors!$A$1:$B$1000,2,0)=1, "Psychologist","Psychiatrist"),"")
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
Hi Kinno,

You can adjust the formula I wrote as follows: (In cell C2 enter)
=IF(SUMPRODUCT(--(B2=$D$2:$D$5))=1,"(Psychologist)",IF(SUMPRODUCT(--(B2=$E$2:$E$3))=1,"(Psychiatrist)",""))

This will return "" (blank) where the Doctor Name does not appear in either the list of Psychologists (D2:D5) or the list of Psychiatrists (E2:E3)

Ofcourse if the list of Psychologists/Psychiatrists is updated with more names then the formula will have to be changed to reflect this.

I hope this helps!
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
Certainly will do!

Just another thought; if the list of Psychologists/Psychiatrists is getting updated fairly regularly, it may be worth making a dynamic named range for each separately, then you can reference the dynamic range in the formula so that it updates automatically.
 

Forum statistics

Threads
1,082,360
Messages
5,364,939
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top