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


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


Any help is greatly appreciated; thanks!

 

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


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


Any help is greatly appreciated; thanks!

I have cross-posted on the following websites:
- mrexcel.com/board/threads/index-match-multiple-results-from-12-different-tables.1195297
- excelforum.com/excel-formulas-and-functions/1370585-index-match-multiple-results-from-12-different-tables.html
- chandoo.org/forum/threads/index-match-multiple-results-from-12-different-tables.47538

 

[p45cal]

A no-formula possibility, see the Results sheet in the attached.
This is just proof of concept; I can only imagine that your data looks nothing like your sample.
What I've done is make all your tables real Excel tables, and given them each a name which starts with tbl.
Then in the background I've added a Power Query query which brings all the tables into one table from which a pivot table is created on the Results sheet.

You don't have to use real Excel tables but it makes things easier. For example they automatically resize themselves when you add/remove rows, it wouldn't matter where in your workbook the tables are (multiple sheets or not).
Yes, if the source tables change, you'll need to refresh the pivot once (right-click and choose Refresh).

 

[AliGW]

I have offered a very similar solution on ExcelForum. No reply from the OP so far.

 

[Saradomin]

A no-formula possibility, see the Results sheet in the attached.
This is just proof of concept; I can only imagine that your data looks nothing like your sample.
What I've done is make all your tables real Excel tables, and given them each a name which starts with tbl.
Then in the background I've added a Power Query query which brings all the tables into one table from which a pivot table is created on the Results sheet.

You don't have to use real Excel tables but it makes things easier. For example they automatically resize themselves when you add/remove rows, it wouldn't matter where in your workbook the tables are (multiple sheets or not).
Yes, if the source tables change, you'll need to refresh the pivot once (right-click and choose Refresh).

Thanks so much for taking the time to offer a solution.

Unfortunately, I have no experience with PowerQuery, and neither do any of the other people that will be using the spreadsheet.

You were absolutely correct when you said "I can only imagine that your data looks nothing like your sample."

The original spreadsheet I attached was a very simple version of the spreadsheet I am actually working with; I thought I would be able to modify the formulas to work with it, but it turns out I overestimated my ability.

I will attach a spreadsheet that is almost identical to what I'm working with (minus the sensitive data).

Cheers.

 

[AliGW]

I'm out - sorry.

Not only do I find it unacceptable when people pepper their queries across so many different forums, I also find it extremely irritating when they present datasets that are very clearly unfit for purpose.

Everything you have done so far has been a waste of at least one person's time. and on at least one of the forums, several people's time.

 

[p45cal]

For a Power Query solution:
1. Could you supply a sample workbook where the headers for the data you want to 'pull' are the real headers used.
2. In your original sample file you had a Paid column with No/Yes. What's the equivalent column in the current file? [Ignore this, I've seen it's column S. If it's not the actual header can you say what it is?]
3. In your original sample file you had the month name as the first header in each table; Is there a similar header (or something else somewhere) you might want to use to differentiate on the Results sheet which sheet data has come from?

 

[Saradomin]

For a Power Query solution:
1. Could you supply a sample workbook where the headers for the data you want to 'pull' are the real headers used.
2. In your original sample file you had a Paid column with No/Yes. What's the equivalent column in the current file? [Ignore this, I've seen it's column S. If it's not the actual header can you say what it is?]
3. In your original sample file you had the month name as the first header in each table; Is there a similar header (or something else somewhere) you might want to use to differentiate on the Results sheet which sheet data has come from?

My apologies for being vague about the data and header labels, etc; the data and labels are very sensitive.

I was trying to do my best to supply a sample spreadsheet, that I could then modify the formulas to work in the actual spreadsheet I'm working with, which is the reason why I asked for an INDEX MATCH solution, because I thought I could work with that.

But people kept offering non-INDEX MATCH solutions that I had no idea how to modify them to work with the actual spreadsheet, and as such, it seems that I have unintentionally irritated some people.

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