Pairing 2 items

I have a set of managers managing different departments. I want to ensure that when a manager is selected, perhaps from a dropdown, it corresponds to the appropriate department. In other words managers and departments should be paired in 2 separate columns or otherwise. Is there a way to do this?


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 11/14/21

    Hello @Vinton Douglas

    There are a few approaches that will work. Depending on the number of departments/managers - and your inclination of changing formulas if a person changes roles, one can use nested IF statements.

    The other option is to set up a reference sheet since the Departments will remain fixed and the people may or may not. This is my preference when I have your situation. If the person changes, you are then updating a name presented in tabular format, rather than updating a formula.

    An Index/Match (or Index/Collect) would then pull the relevant data into the field on your main sheet.