i am creating an employee contact list for my company. One of the columns is "Departments" (a choice column n=5). In each Department, there are several teams. Because Employees will need to enter/update their information, I made 5 "Teams" choice columns so that if an employee select their department, only the teams titles in that department appear. so far I am good.

Now my issue is, because those are five distinct columns, visualizing the information is not the best. I would like to be able to see all Team's title values in one column instead of 5 different columns (and avoid the blanks).


Column 1: Department - choice (text) - a, b, c, d, e;

Column 2: Team A - choice (text) - aa, bb, cc, ee;

Column 3: Team B - choice (text) - aa, bb, cc, ee;

Column 4: Team C - choice (text) - aa, bb, cc, ee;

Column 5: Team D - choice (text) - aa, bb, cc, ee;

Column 5: Team E - choice (text) - aa, bb, cc, ee; what I want:

What I want

Column 6:

if (column 1)== a, show text value of what was chosen in (column 2), if (column 1) == b, show text value of what was chosen in (column 3), etc.

I am new to this so any help is much appreciated. i am using MS2016. my goal is to have this as an app.

Thank you

1 Answer 1


You'll want a calculated column and use the "IF" function.

The formula would need to be something like

=IF([Department]="A",[Team A],IF([Department]="B",[Team B],IF([Department]="C",[Team C],IF([Department]="D",[Team D],IF([Department]="E",[Team E],"")))))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.