Nov 16 2022 01:15 PM - edited Nov 16 2022 01:23 PM
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.
Nov 16 2022 01:37 PM
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]],"")
Nov 16 2022 01:50 PM
Nov 16 2022 01:53 PM - edited Nov 16 2022 01:54 PM
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.
Nov 16 2022 02:00 PM
Nov 16 2022 02:16 PM
SolutionGot it. I put together a sample workbook with a similar arrangement.
=XLOOKUP([@Facility]&1,TableB[Facility]&TableB[WOAR],TableB[Local Administrator],"")
Nov 16 2022 03:36 PM
Nov 16 2022 03:40 PM - edited Nov 16 2022 03:41 PM
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.
Jan 04 2023 12:15 PM - edited Jan 04 2023 12:22 PM
@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)
Jan 04 2023 01:46 PM
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)
Feb 14 2023 05:57 AM
@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.
Feb 14 2023 06:53 AM - edited Feb 14 2023 07:30 AM
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")
Feb 14 2023 07:18 AM
Feb 14 2023 07:32 AM
Nov 16 2022 02:16 PM
SolutionGot it. I put together a sample workbook with a similar arrangement.
=XLOOKUP([@Facility]&1,TableB[Facility]&TableB[WOAR],TableB[Local Administrator],"")