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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

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,373
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,127,100
Messages
5,622,710
Members
415,922
Latest member
gemmatay88

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
Top