Forum Discussion

jptheii's avatar
jptheii
Copper Contributor
Nov 16, 2022
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...
BI & Data Analysis
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Nov 16, 2022

    jptheii 

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

     

    =XLOOKUP([@Facility]&1,TableB[Facility]&TableB[WOAR],TableB[Local Administrator],"")
jptheii's avatar
jptheii
Copper Contributor
Nov 16, 2022
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.
Patrick2788's avatar
Patrick2788
Silver Contributor
Nov 16, 2022

jptheii 

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

 

=XLOOKUP([@Facility]&1,TableB[Facility]&TableB[WOAR],TableB[Local Administrator],"")
  • jptheii's avatar
    jptheii
    Copper Contributor
    Nov 16, 2022
    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?
    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Nov 16, 2022

      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.

      • jptheii's avatar
        jptheii
        Copper Contributor
        Jan 04, 2023

        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)