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!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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")
 
Upvote 0
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:
Upvote 0
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!
 
Upvote 0
#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"),"")
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top