INDEX MATCH | Multiple Results From 12 Different Tables

Hello everyone.

I am having some trouble figuring out how to pull multiple results from multiple tables using INDEX MATCH.

I have 12 tables (one for each month), and I'm trying to make an annual table that pulls specific data (based on a single criterion) from the 12 monthly tables.

After Googling some similar problems, I've managed to get a working formula to pull data from two tables, but I can't figure out how to pull data from all 12 tables.

The formula I have so far is:
{=IFERROR(IFERROR(INDEX($A$2:$A$6,SMALL(IF(ISNUMBER(MATCH($B$2:$B$6,$B$15,0)),MATCH(ROW($B$2:$B$6),ROW($B$2:$B$6)),""),ROWS($A$1:A1))),INDEX($D$2:$D$6,SMALL(IF(ISNUMBER(MATCH($E$2:$E$6,$B$15,0)),MATCH(ROW($E$2:$E$6),ROW($E$2:$E$6)),""),ROWS($A$1:A1)-COUNTIF($B$2:$B$6,$B$15)))),"")}

I have attached a sample file of what I'm working with.

Any help is greatly appreciated; thanks!

Edit:

I have cross-posted on the following websites:
https://www.mrexcel.com/board/thread...ables.1195297/
https://chandoo.org/forum/threads/in...-tables.47538/
https://www.excelguru.ca/forums/show...fferent-Tables

I think that, by making a table for each month, you are making more work for yourself than necessary. If you has a single table for all months, your extract would be far simpler.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

Hello.

I didn't realise that "cross-posting" was a potential problem.

I tried to edit my post to include the cross-post links, but I was met with the following error:

"The following errors occurred with your submission:
You are not allowed to post any kinds of links, images or videos until you post a few times."

So instead of posting the links, I posted the website names; I hope that is sufficient considering the circumstances.

Originally Posted by Saradomin

Hello....
So instead of posting the links, I posted the website names; I hope that is sufficient considering the circumstances.

Thats great, thanks

May I ask, did all data are in separated sheet ? (ie. one month each ?) and in the same structure ?
And how you deal with the same client that appear in multiple sheet with the same condition ?

Regards.

Note : I'm not sure is it ok to just post the website name for cross-linked post.

Originally Posted by menem

May I ask, did all data are in separated sheet ? (ie. one month each ?) and in the same structure ?
And how you deal with the same client that appear in multiple sheet with the same condition ?

Hey,

Yeah, each month has its own table in its own sheet (so, 12 tables across 12 sheets).

What I'm trying to do is create a 13th table on a 13th sheet, that pulls all the unpaid clients from all 12 monthly tables into a single annual table.

In terms of duplicate clients, each entry has its own unique identifier, so hopefully I won't run into any issues there.

What are your thoughts?

Thanks

Edit:
I've added an excel file to try to better explain what I mean.

This can be done with PowerQuery. If you would like me to show you how, please say. If you don't want to use PQ, then please also let me know.

If you don't mind to use many of helpers.

1 try to count how many occur of criteria in each sheet.

J5:J16 type name of sheets

K5=COUNTIF(INDIRECT("'"&J5&"'!C:C"),$B$2)
copy down to K16

I5=0
I6=+I5+K5
copy down to I16

2 build lookup rows of occurance
E2=500 ( becuase I need to use match so this number can reduce range of rows to lookup / increase of decrease as needed )

D5=IF(ROWS(D$4:D4)-1<SUM($K$4:$K$16),VLOOKUP(ROWS(D$4:D4)-1,$I$4:$J$16,2,TRUE),0)

E5=IF(D5<>0,IF(D4=D5,E4+1,1),0)

F5=IF(E5>0,IF(D4<>D5,
MATCH($B$2,INDIRECT("'"&D5&"'!C:C"),0),
MATCH($B$2,INDIRECT("'"&D5&"'!C"&F4+1&":C"&F4+$E$2),0)+F4),
0)

all copy down as need

3 answer area
B5=IF(E5>0,INDIRECT("'"&D5&"'!A"&F5),"")

Regards.

@Ali: as a complete PQ ignoramus, I would welcome instruction on how to address this using PQ. I have created tables in each sheet but where to go from here ...????

UPDATE: now managed to combine (append) thanks to "You Tube" !

Meanwhile a VBA offering

Sub Create_Summary()
Dim ar(), arr()
Dim ws As Worksheet
Dim Current As Worksheet
Set ws = Worksheets("Results")

n = 0
Criteria = Range("Criteria")  ' Selection Citeria

' Loop through all of the worksheets in the active workbook.

For Each Current In Worksheets

   If Current.Name <> "Results" Then
   
       With Current
       
           lr = .Cells(Rows.Count, 1).End(xlUp).Row
           ar = .Range("A2:C" & lr)
           For i = 1 To UBound(ar, 1)
               If ar(i, 3) = Criteria Then  ' Criteria Matched then ....
                   n = n + 1
                   ReDim Preserve arr(1 To n)
                   arr(n) = ar(i, 1)  ' Store Client Number
               End If
           Next i
           
       End With
       
    End If
Next
         
  ws.Range("B5:B100").ClearContents
  ws.Range("B5").Resize(UBound(arr, 1), 1) = Application.Transpose(arr)
  
End Sub

Just for those who might be interested (e.g. John), attached is my PQ solution.

It uses:

1. Named ranges for each month - can be set as long as they will ever need to be.
2. =Excel.CurrentWorkbook() in a blank query to pull the named rnages - filter this list to ignore anything beginning with Table or Results.
3. The columns are expanded out (deselect the pesky tick box bottom left in the dialog) and then filtered to records containing No.
4. Remove unwanted columns then Close & Load To ... your selected destination.

I've change relative cells of formula (use the same logic as above)

except the output area have changed to
A3
=IFERROR(INDEX(INDIRECT("'"&$R3&"'!1:1000000"),$T3,COLUMN(A$1)),"")

Regards.


Note : Please maintain formula in column R,S,T equal to or more than the output.
and you may change the red text to identify column number you want for each output.

Menem ... this is ... absolutely perfect!

Thank you so much!

I think this completely solves my problem and I am very grateful to you

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.