Auto-updating a cell depending on input from another cell

[xChristy]

Good day everyone!

So I have this excel table containing 2 columns : "Department" and "Incumbent Position" (it normally also contains name and other data of the employees but I have reduced it to this).
Every department has a number of specific positions, and what I am looking for is a way so that if I input a certain position for an employee, the "Department" cell will update to the Department that correlates to that position. I hope I am explaining this well enough.

So if I want to update the position of John as "Clinical Research Associate", I want Excel to automatically update the "Department" of John to "Clinical Operations".
The Excel table has 2 Sheets: "Employee List" and "Positions".

Inside the "Positions" sheet, I have created a table with every department on the first row and their specific positions below.

What formula can give me the possibility of doing this? Is it possible to do it without the second sheet(not necessarily, but I'm curious)?

Thanks in advance!

Example.xlsx

[ChemistB]

Not sure where you are looking to put this formula but in a hypothetical sheet where you have the employee name in A2 and his/her position in B2, in C2, this formula will give you the Department

Please Login or Register  to view this content.

IF you wanted to pull the department from your "Positions" tab

Please Login or Register  to view this content.

[ORoos]

Hi xChristy,

Would be good if you include a sample of the expected outcome in your sample worksheet.
Anyway, I assume you want to enter a name and position on the 'Employee List' sheet.
Column A is probably the manual link for the Department and Position.
To automate the department listing, enter the position in column B, the employee name in column A (if you no longer need the manual linked list) or column E...
In cell F2 try the formula below and pull down.

Please Login or Register  to view this content.

The formula will return the Department (Column Header) under which the position is found.

PS: You are missing the CEO / BoD in your P

[xChristy]

Thank youuu! Both of your solutions work as I needed. I truly appreciate the help!