I have two tables in my excel, one of them contains the departments of my business and the other one contains my employees. My idea was to add a column in the employees table that shows if they are free or not if they are included on the other table. I've checked vlookup, xlookup... but i'm not sure if these are mean to be what i need. I tried these but they were not working as i expected / properly. The problem here is that one employee may exist in multiple departments, so adding a column of departmen in staff table is not an option.

First table

Second table

CodePudding user response：

You can use COUNTIF across the entire Departments table to check whether the employee exists in that table. If they do then it returns 'No' (they're not free). If they do not exist in that table then it returns 'Yes'.

=IF(COUNTIF(Departments[#All],[@Staff])>0,"No","Yes")