H-Stack based on combination of 3 uneven data sets

[Hunter Childress]

In the attachment, I am trying to populate the table on Template 2. The first three columns use HSTACK based on the 3 data sets on the right, combining them to have every combination. Then pull in the data for each month of each tab based on the group which is in cell B2 on each of the orange tabs.

In the end, it would be a complete dataset pulling in the values from each tab for Jul - Dec.

Open to ideas but prefer not to use VBA.

Thank you.

 

[djc]

Since each of the orange tabs have the same layout, with the group in cell B2, the entity in cell B4 and all of the accounts listed in range A23:A156, you could try something like this to get the desired results:

=LET(
   a, VSTACK('2100:3800'!A23:O156),
   n, SHEETS('2100:3800'!B2),
   j, SEQUENCE(,ROWS(a)/n),
   e, TOCOL(IF(j,VSTACK('2100:3800'!B4))),
   g, TOCOL(IF(j,VSTACK('2100:3800'!B2))),
   HSTACK(e,g,CHOOSECOLS(a,1,XMATCH(D3:I3,'2100'!A22:O22)))
)

 

[Hunter Childress]

Thank you, Thank you, Thank you!!! You crushed this. I deeply appreciate it.

 

[p45cal]

In the attached (a separate workbook which interrorgates your attached file) you'll find:
1. Cell A1, a named range (called FileNameAndPath) containing the full path and file name of the workbook to be interrogated which you should adjust.
2. A copy of your combinations table. This is used to filter the results.
3. A results table at cell G3. Should your source data change or the data in the combinations table change, the results table will need updating by right-clicking somewhere in it and choosing Refresh.

Here I've severely reduced the size of the combinations table to just 3 data rows and you can see how it affects the results:





There are probably 2 too many columns than you want to see in the results but that's easy enough to tweak.