Forum Discussion

Debs_au's avatar
Debs_au
Copper Contributor
Jul 14, 2021

Can you use table structured references with a 3D reference Same column through multiple tables.

Hi All,

I'm using Windows 10 home ver 20H2, 64 bit Op sys, build 19042.1052, with Excel ver 365.

 

I want to know if its possible to use a 3D reference in a structured reference formula.

I have one file containing several tables, each on a separate worksheet, with each having exactly the same column format. The only difference being the number of rows in each table, and the data they contain.

 

I am currently using the XLOOKUP function to lookup each member number from the Membership table, on Table01 class list, and return the attendance data in that class for that member.

=XLOOKUP(Table_Membership[@[Member'#]],Table01[[All],[Member'#]],Table01[[All],[Attendance]])

that works for Table01; but would like to know if its possible to reference a 3D formula syntax that uses Table01:Table20, for both the search criteria in column [Member] and the return criteria [Attendance].

 

Any ideas gratefully received.

Excel
Formulas and Functions
  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    Jul 15, 2021
    you can do a data consolidate to make one big table00 for table01:table20, then do a search on table00
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jul 15, 2021

    Hi Debs_au 

     

    Given that your tables appear to be consistenly named (Table01, Table02...Table20) with exactly the same columns it would be pretty straightforward to consolidate them all as a big Table with Power Query. You would then perform your XLOOKUP on that big Table

    Is this an option for you? Do you need a sample?

     

    Note: if you keep the same naming convention for your tables, the query can auto. pick any additional table, i.e. Table21, Table25...

    • Debs_au's avatar
      Debs_au
      Copper Contributor
      Jul 16, 2021
      Hi L z,
      I was thinking along those lines to use a Pivot table. Is Power Query the same thing or different?
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jul 16, 2021

        Hi Debs_au 

         

        In short: Power Query & Pivot Tables are 2 different things but the first can help the second...

         

        Not clear to me what you ultimately want to achieve. However, assuming you want to build a Pivot Table from the data in 20 Tables, in any case you need Power Query to "take" your 20 Tables and consolidate them as 1 big Table that would be the Source for your Pivot Table

         

        Note that if you build a Pivot Table out of the Power Query query, you don't necessarily have to load the big Table on a worksheet

         

        Hope this clarifies things a bit

Resources