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

jglassnerHI

New Member
Joined
Feb 2, 2016
Messages
19
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
2020-10-30_12-00-45.jpg


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

2020-10-30_12-04-42.jpg


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

Jason
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

jglassnerHI

New Member
Joined
Feb 2, 2016
Messages
19
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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)

2020-10-30_12-48-17.jpg
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,168
Office Version
  1. 2016
Platform
  1. Windows
Hi JGlassnerHI,

Something like this?

JGlassnerHI.xlsx
ABCDEFGHIJK
1Department Chief:Doc EmmetDepartmentCardiologyDepartmentWork LocationsIslandDepartment Chief
2Allergy ImmunologyClinic 11Doc Hoo
3CardiologyClinic 32Doc Emmet
4Work LocationClinic 3A&EClinic 33Doc Quinn
5Allergy ImmunologyClinic 21Doc Quinn
6CardiologyClinic 22Doc Quinn
7A&EClinic 13Doc Hoo
8Allergy ImmunologyClinic 14Doc Emmet
9CardiologyClinic 23Doc Hoo
10A&EClinic 12Doc Emmet
Sheet1
Cell Formulas
RangeFormula
B1B1=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
CellAllowCriteria
E1List=$H$2:$H$13
E4List=$I$2:$I$10
 

jglassnerHI

New Member
Joined
Feb 2, 2016
Messages
19
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,809
Messages
5,574,435
Members
412,592
Latest member
moonsugar
Top