Choose Values from Different Drop-down list to return Third Value

[jglassnerHI]

I am looking for help with looking up two values to return a third.

My thought is a VLOOKUP something like this =VLOOKUP(Y5&Y6,data,column,0) but this doesn't work. Is the issue since I am looking up from the drop-down menu? Both columns have duplicate values. Example: We have the same department 9 different times but only 3 chiefs.

On the report page, I have two drop-down menus. I want to select from Y5 and Y6 to return the department chief.

1. Department
2. Work Location
3. I want it to return the department chief name

All of this information is in a table named "Shared Dataset"

Thanks for your help, I have watched different videos and some of them are really complicated and the simple ones do not work.

Jason

 

[jglassnerHI]

I also tried creating a lookup table combining two columns and use that for the lookup value in a VLOOKUP. The result I need is in column 6

=VLOOKUP('[Clinic Support Team Directory.xlsx]Clinic Support Team'!$B:$B&" | "&'[Clinic Support Team Directory.xlsx]Clinic Support Team'!$C:$C,6,0)

 

[Toadstool]

Hi JGlassnerHI,

Something like this?

JGlassnerHI.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Department Chief:	Doc Emmet		Department	Cardiology			Department	Work Locations	Island	Department Chief
2								Allergy Immunology	Clinic 1	1	Doc Hoo
3								Cardiology	Clinic 3	2	Doc Emmet
4				Work Location	Clinic 3			A&E	Clinic 3	3	Doc Quinn
5								Allergy Immunology	Clinic 2	1	Doc Quinn
6								Cardiology	Clinic 2	2	Doc Quinn
7								A&E	Clinic 1	3	Doc Hoo
8								Allergy Immunology	Clinic 1	4	Doc Emmet
9								Cardiology	Clinic 2	3	Doc Hoo
10								A&E	Clinic 1	2	Doc Emmet
Sheet1

Cell Formulas
Range	Formula
B1	B1	=IFERROR(INDEX($K$2:$K$10,AGGREGATE(15,6,ROW($H$2:$H$10)-ROW($H$1)/(($H$2:$H$10=$E$1)*($I$2:$I$10=$E$4)),1)),"No Chief")

Cells with Data Validation
Cell	Allow	Criteria
E1	List	=$H$2:$H$13
E4	List	=$I$2:$I$10

 

[jglassnerHI]

thanks this is impressive.

I think what is tripping me up is the lists. For Department and Specialty, there are duplicate values. I first need to create cascading drop-down lists, two total and the output from the two dropdowns will be the chief name. Maybe you said this in the formula but I'm assuming that I need to work on the two drop down first. I have watched videos to near-death trying to figure this out.

The data is on a separate worksheet within the same document. Which is linked to an Access database.