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:



Excel file is attached.

Any help is greatly appreciated; thanks!

 

[bosco_yip]

This formula solution worked for Excel 219 or above.

In B17, formula copied down :

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,A$2:B$6,D$2:E$6,G$2:H$6,J$2:K$6,M$2:N$6,P$2:Q$6,A$9:B$13,D$9:E$13,G$9:H$13,J$9:K$13,M$9:N$13,P$9:Q$13)&"</b></a>","//b[following::*[1]='"&B$15&"']["&ROW(A1)&"]"),"")

 

[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 77713

Excel file is attached.

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
- excelguru.ca/forums/showthread.php?11449-INDEX-MATCH-Multiple-Results-From-12-Different-Tables

 

[Saradomin]

This formula solution worked for Excel 219 or above.

In B17, formula copied down :

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,A$2:B$6,D$2:E$6,G$2:H$6,J$2:K$6,M$2:N$6,P$2:Q$6,A$9:B$13,D$9:E$13,G$9:H$13,J$9:K$13,M$9:N$13,P$9:Q$13)&"</b></a>","//b[following::*[1]='"&B$15&"']["&ROW(A1)&"]"),"")

View attachment 77717

This is brilliant, thank you!

Would this method work if each of the 12 tables were on their own sheet, opposed to all being located on a single sheet?

I tried to modify the formula you gave to work across multiple sheets and, well, it didn't go very well

I have attached a file as an example of what I mean, thanks!

 

[bosco_yip]

Try,

1] Create a data validation "dropdown list" by :

Select A1, click Data >> Data Validation >>

• Allow : List
• Source : $K$2:$K$13

2] In "Result" B5, formula copied down :

=IFERROR(INDEX(INDIRECT($A$1&"!$A:$A"),AGGREGATE(15,6,ROW($C$2:$C$100)/(INDIRECT($A$1&"!$C$2:$C$100")=B$2),ROW(A1))),"")

3] Click A1 "dropdown list", change the "Sheet name" and check your results

 

[Saradomin]

Try,

1] Create a data validation "dropdown list" by :

Select A1, click Data >> Data Validation >>

• Allow : List
• Source : $K$2:$K$13

2] In "Result" B5, formula copied down :

=IFERROR(INDEX(INDIRECT($A$1&"!$A:$A"),AGGREGATE(15,6,ROW($C$2:$C$100)/(INDIRECT($A$1&"!$C$2:$C$100")=B$2),ROW(A1))),"")

3] Click A1 "dropdown list", change the "Sheet name" and check your results

View attachment 77721

Hey, thanks again for your assistance!

This does work, and I'm sorry if I wasn't clear or didn't explain properly, but this isn't quite what I'm trying to achieve.

I need the results to display the unpaid clients for all 12 months in a single annual table, without having to manually choose a month with a drop-down list.

So I need the results to display:
Client 3
Client 5
Client 6
Client 19
Client 21
Client 30
Client 33
Client 47
Client 54
Client 60

What are your thoughts?

And thanks again so much!

 

[bosco_yip]

Try,

In "Result" sheet B5, formula copied down :

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,January:December!A$2:C$100)&"</b></a>","//b[following::*[2]='"&B$2&"']["&ROW(A1)&"]"),"")

 

[Saradomin]

Try,

In "Result" sheet B5, formula copied down :

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,January:December!A$2:C$100)&"</b></a>","//b[following::*[2]='"&B$2&"']["&ROW(A1)&"]"),"")


View attachment 77726

Hi, sorry for the late reply.

This is fantastic, thank you so much!

As you may have guessed, I haven't provided the actual spreadsheet as it contains sensitive information.

I figured I would just provide a sample spreadsheet with simple tables, and then edit the formulas to work with the larger tables I have.

But I've drastically overestimated my ability and ... I can't get it working at all

I will attach a file with the tables that I am actually working with (minus the real data), and I was hoping you could take a look for me.

I really appreciate your continued help with all of this, especially after I keep foolishly changing the scope of my problem!

 

[Saradomin]

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