I have a sheet with a list of company names in column A - some of these company names are present within sheets of different users, who have a tickbox within a column beside these companies.

I want to have a column in my sheet which would tell me whether this company is ticked within one of the user's sheets and if so, put the user's name in the cell next to it. If not, it can remain blank. The likelihood at this time that the tickbox is TRUE for the same company in two different sheets is negligible.

In the sample sheet below, I've used the following formula in cell B2:

=ifs(vlookup(A2,Sheet2!$A$2:$B$11,2,false)=TRUE,"Sam",vlookup(A2,Sheet3!$A$2:$B$11,2,false)=TRUE,"Nick",vlookup(A2,Sheet4!$A$2:$B$11,2,false)=TRUE,"Mike")

It works for the first two conditions, but then it cannot seem to work with the third logical pair. Note that the sample sheet below uses the vlookup across tabs, the actual sheet will be using importrange, but I don't think it should make a difference.

What could be wrong?

Sample sheet