Value depending on 2 different columns of data

[dmg1127]

I have three columns with large amounts of data
Column A: Office Location, Column B: Coordinator Name, Column C: Ops Manager Name

In cell D1 of my spreadsheet, i have a dropdown list that has all the office locations
I choose the location
Cell E1 needs to be a formula that shows me who the coordinator is for the location I selected
Cell F1 needs to be the Ops Manager for that location.

I need cells E1 and F1 to auto populate based on the location that I selected

any help would be greatly appreciated!
Thanks

 

[Joe4]

Welcome to the Board!

You can create a lookup table of each office and the Coordinator and Ops Manager associated with each one, and then use a VLOOKUP formula to look up those other values you want.
See: VLOOKUP Function

 

[awoohaw]

try this:

Mr excel questions 47.xlsm
	A	B	C	D	E	F
1	location	coordinator	manager	Select	Coordinator	Manager
2				East	Sharon	Jane
3	North	John	George
4	Northeast	Bill	William
5	East	Jane	Sharon
6	Southeast	Ann	Michelle
7	South	Tom	Robert
8	Southwest	Joe	Charles
9	West	May	Samantha
10	Northwest	Meg	Rachel
11
dmg1127

Cell Formulas
Range	Formula
E2	E2	=VLOOKUP($D$2,$A$3:$C$10,3,0)
F2	F2	=VLOOKUP($D$2,$A$3:$C$10,2,0)

Cells with Data Validation
Cell	Allow	Criteria
D2	List	=$A$3:$A$10

 

[dmg1127]

that worked like a charm. thank you so much!