Formula to return value looking up multiple fields

[mrwinter]

I'm trying to create a field which lists a user's two heads of department (HoD) for each building.

I have all the pieces I need in my current table, but am struggling to find the best way of looking up the data to return.

I have the following fields for every user: Building (Single Select), Department (Single line text), HOD (formula returning true/false - there are two HoDs for each department) and would like to have a field called User's HoD that returns the two users who match Building, Department and HoD = true.

Any suggestions would be really appreciated!

[TheTimeSavingCo]

I don't think you'll be able to get what you want with formulas alone I'm afraid

I think you'll need to have the departments in another table with two linked fields to your users table.  The first linked field would be for the "Head of department", and would be used to indicate which department the person is the head of, and the second linked field would be used to indicate which department the person is, resulting in something like this:

Link to base

[mrwinter]

Hi Adam,

Thanks for your suggestions. Rather than use a separate table I ended up using an automation to watch whether a record had an empty HODs field but a valid building and department, then find records matching building, department, and whether a user was marked as an HOD. This got me a list of accounts I could populate the HODs field with.

Thanks again for your help.