Linking Rows Between Columns

#1

I am looking for a way to make rows “point” to each other by linking them. Each row is a room and each room is either a classroom or a service room. I want to link service rooms to their associated classroom in one column and have another column that if the room is a main room, looks for service rooms linked to the main room and returns the list of associated service rooms in a list.

I tried doing this with various combinations of lookup and linked fields as well as a nested IF formula and Array function with partial success. My backup plan is to just have two columns of linked fields and link the rooms to each other manually, but I was looking to automate this as much as possible and would like to have “N/A” or similar in the “Associated Main Room” column if the room is a classroom/main room or “N/A” in the “Associated Service Room” column if the room is a service room.