If value in list, then assign label

[Kinno]

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.

[TABLE="width: 600"]
<tbody>[TR]
[TD]Patient No.[/TD]
[TD]Doctor Name[/TD]
[TD]Doctor Role[/TD]
[TD][/TD]
[TD]Psychologist[/TD]
[TD]Psychiatrist[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Ollie Ollison[/TD]
[TD](Psychologist)[/TD]
[TD][/TD]
[TD]Ollie Ollison[/TD]
[TD]Michael Michaelson[/TD]
[/TR]
[TR]
[TD]1235[/TD]
[TD]Phil Philson[/TD]
[TD](Psychiatrist)[/TD]
[TD][/TD]
[TD]David Davidson[/TD]
[TD]Phil Philson[/TD]
[/TR]
[TR]
[TD]1236[/TD]
[TD]David Davidson[/TD]
[TD](Psychologist)[/TD]
[TD][/TD]
[TD]Laura Laurenson[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1237[/TD]
[TD]David Davidson[/TD]
[TD](Psychologist)[/TD]
[TD][/TD]
[TD]Steve Stevenson[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1238[/TD]
[TD]Laura Laurenson[/TD]
[TD](Psychologist)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1239[/TD]
[TD]Michael Michaelson[/TD]
[TD](Psychiatrist)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1240[/TD]
[TD]Phil Philson[/TD]
[TD](Psychiatrist)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

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]

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.

 

[Kinno]

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]

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

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!

 

[Kinno]

Perfect thank you so much! Exactly what I needed

Have a nice day both of you

 

[tyija1995]

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.