If, Countifs Formula Dynamics Table Array Error

[jptheii]

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 tables in the workbook (approximately 12 MB). 

I have created a if, countifs formula in a table for if two criteria apply, then provide the value in a column in a table. It is structed like =if(countifs(TableB[Name],[@[office.y]],TableB[WOAR],1), TableB[Local Administrator],""). Unfortunately, when the argument is true, at its first encounter it generates a #SPILL error. Through research, I have discovered the issue here is it is a dynamics array error because it cannot SPILL in a table. I have very similar formulas which work but the true value is a static, exact value (e.g., "Y", or "Y, WOAR"). I have tried all manner of different approaches, but it appears the failure is because the return/true value is a dynamic value from within a table. 

Can someone inform me if I am approaching this wrong? Is there another way to accomplish the same thing? If a row in a table meets various criteria, I want it to return the value in a specified column in that row. 

[#2]

@jptheii 

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]],"")

[#3]

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?

[#4]

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.

[#5]

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.

[#6 · Accepted solution]

@jptheii 

Got it. I put together a sample workbook with a similar arrangement.

=XLOOKUP([@Facility]&1,TableB[Facility]&TableB[WOAR],TableB[Local Administrator],"")

[#7]

Thank you again! This looks like it will work. Last question. I understand the formula besides the 1 in "&1". What is the purpose of the 1?

[#8]

You mentioned that if a facility from TableA is in TableB and has a 1 in another column, to return the local admin's name. The 1 is concatenated to the lookup value to simplify the formula so there's no need for IF or COUNTIFS.

[#9]

@Patrick2788 thank you! Happy New Year! I am back again!

I am trying to match something using xlookup. I have Table A and Table B (Table 17) on two different tabs in the same workbook. Table A is where the formula is being inputted into a cell. The formula tries to match the facility name and requestor name in two separate columns in Table A, to the facility name and facility authorizer name in two separate columns in Table B.

I am trying to use a wildcard so it matches the last name located in a column on Table A to the full name located in a column on Table B.

My goal is to determine through formula if the requestor of a facility on Table A has a last name that matches the person in Table B (facility authorizer column) for the same facility. Facility authorizers can automatically have their account request approved.

I continue to receive the #VALUE! error. The formula is below. 

=XLOOKUP([@[Office.y]]&"*"&[@[Requestor Last Name]]&"*",Table17[Name]&Table17[Facility Authorizer Name],[Requestor Last Name],"Not Likely",2)

[#10]

@jptheii 

I may need to see the workbook, but you might try specifying the Table name before the bolded:

=XLOOKUP([@[Office.y]]&"*"&[@[Requestor Last Name]]&"*",Table17[Name]&Table17[Facility Authorizer Name],[Requestor Last Name],"Not Likely",2)

[#11]

@Patrick2788I FINALLY was able to spend enough time stripping down the document. It contained a lot of data and deleting it took processing time, and thinking through stripping it of PII took time as well. Nevertheless, the document is now attached.

I appreciate you taking a look at this.

[#12]

@jptheii 

I'd use XMATCH instead of XLOOKUP:

=IF(AND(IFERROR(XMATCH([@[Office.y]],Table17[Name]),0),IFERROR(XMATCH("*"&[@[Requestor Last Name]]&"*",Table17[Facility Authorizer Name],2),0)),[@Column4],"Not Likely")

[#13]

Thank you again. I believe I understand everything else, but could you explain what "[#Data],[#Totals]" mean?

[#14]

A slip of the mouse. I've updated the above formula because those aren't needed. [#Data] refers to the data in the table (w/o header) and [#Totals] points to the totals row. Those were being inadvertently added to the reference when I selected the entire column.