Forum Discussion
Solved
If, Countifs Formula Dynamics Table Array Error
We have established an internal tracking system. This system relies upon a single workbook with multiple worksheets, but has formulas that are very interwoven and interdependent. There are over 20 ta...
Got it. I put together a sample workbook with a similar arrangement.
=XLOOKUP([@Facility]&1,TableB[Facility]&TableB[WOAR],TableB[Local Administrator],"")
The bolded is the part the table doesn't like:
=if(COUNTIFS(TableB[Name],[@[office.y]],TableB[WOAR],1), TableB[Local Administrator],"")
Perhaps this:
=if(COUNTIFS(TableB[Name],[@[office.y]],TableB[WOAR],1), [@[Local Administrator]],"")
Thank you for the swift reply! To be sure, the [@[office.y]] is a column in the table of the formula under review. The TableB[LocalAdministrator] portion is in a different table on a different tab of the same workbook. It appeared using the @ was only something automatically assigned from within the same table. If I just use the @ it will know it is from a different table in a different sheet?
I see, what are you looking to return from TableB[LocalAdministrator] ? We'd have to pick a single value out of the array to eliminate the SPILL error.
- I should have used more sensible aliases. TableA is Table1 and TableB is Table 2. Table1 on Sheet 1 is for all pending account requests which lists the facility the request was made for, along with many other columns for analysis purposes. Table 2 on Sheet 2 lists the facility details and our point of contact at that facility. What I want the formula to tell me is if the account request on Table 1 and Sheet 1 lists a facility that exists on Table 2 on Sheet 2, and Table 2 on Sheet 2 has a 1 in the WOAR column, then give me the value listed in the Local Administrator(s) Name column on Table 2 on Sheet 2.
Got it. I put together a sample workbook with a similar arrangement.
=XLOOKUP([@Facility]&1,TableB[Facility]&TableB[WOAR],TableB[Local Administrator],"")