INDEX MATCH | Multiple Results From 12 Different Tables

[Saradomin]

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)))),"")}

And here is a sample image of what I'm working with:

Any help is greatly appreciated; thanks!

 

[Fluff]

Hi & welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: INDEX MATCH | Multiple Results From 12 Different Tables
and here INDEX MATCH | Multiple Results From 12 Different Tables
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Saradomin]

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)))),"")}

And here is a sample image of what I'm working with:

View attachment 57244

Any help is greatly appreciated; thanks!

I have cross-posted on the following websites:
- chandoo.org/forum/threads/index-match-multiple-results-from-12-different-tables.47538
- excelforum.com/excel-formulas-and-functions/1370585-index-match-multiple-results-from-12-different-tables.html
- excelguru.ca/forums/showthread.php?11449-INDEX-MATCH-Multiple-Results-From-12-Different-Tables

 

[Domenic]

Try the following . . .

B16:

=COUNTIF(B2:Q13,"No")

B18, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS($B$18:B18)<=$B$16,INDIRECT(TEXT(AGGREGATE(15,6,((ROW($A$2:$P$13)*10^5)+COLUMN($A$2:$P$13))/($B$2:$Q$13=$B$15),ROWS($B$18:B18)),"R0C00000"),FALSE),"")

Note that INDIRECT is a volatile function.

Hope this helps!

 

[Saradomin]

Hi & welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: INDEX MATCH | Multiple Results From 12 Different Tables
and here INDEX MATCH | Multiple Results From 12 Different Tables
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Hello, thanks for the information.

I wasn't aware that "cross-posting" was a potential problem.

I couldn't figure out how to edit my original post, so I just replied to it with the cross-post information; I hope that's sufficient.

 

[Saradomin]

Try the following . . .

B16:

=COUNTIF(B2:Q13,"No")

B18, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS($B$18:B18)<=$B$16,INDIRECT(TEXT(AGGREGATE(15,6,((ROW($A$2:$P$13)*10^5)+COLUMN($A$2:$P$13))/($B$2:$Q$13=$B$15),ROWS($B$18:B18)),"R0C00000"),FALSE),"")

Note that INDIRECT is a volatile function.

Hope this helps!

Hey,

Thanks so much for your reply; that helps a lot and I have learnt something new!

It certainly works on my end, nice!

Would this method work if each monthly table was on its own sheet, as well as the results being on its own sheet as well?

For example:

Note that I didn't post pictures of every single sheet; just January and Results.

I tried to modify the formula you gave me, but it didn't go so well

 

[Fluff]

How many rows of data do you actually have on each sheet?

 

[Saradomin]

How many rows of data do you actually have on each sheet?

Each table on each sheet has 50 rows and 20 columns.

One of those columns is the Yes/No criteria, and of those 20 columns I need to pull the data across from 4 of them.

Is there no way to attach an Excel file on this forum?

 

[Fluff]

Is there no way to attach an Excel file on this forum?

No, we do not allow files to be uploaded to the site.
I was thinking of a similar solution to the one that bosco_bip suggested on Chandoo, but that won't work with your true data.
Your best bet is probably Power Query, but that is something I know nothing about.

 

[Saradomin]

The user "menem" has offered a solution on excelforum that is working for me