Results 1 to 8 of 8

Thread: INDEX MATCH | Multiple Results From 12 Different Tables

  1. #1
    Neophyte Saradomin's Avatar
    Join Date
    Feb 2022
    Posts
    4
    Articles
    0
    Excel Version
    2019

    INDEX MATCH | Multiple Results From 12 Different Tables



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Neophyte Saradomin's Avatar
    Join Date
    Feb 2022
    Posts
    4
    Articles
    0
    Excel Version
    2019
    Quote Originally Posted by Saradomin View Post
    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

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,115
    Articles
    0
    Excel Version
    365
    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).
    Last edited by p45cal; 2022-02-08 at 02:39 PM.

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,762
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    I have offered a very similar solution on ExcelForum. No reply from the OP so far.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Neophyte Saradomin's Avatar
    Join Date
    Feb 2022
    Posts
    4
    Articles
    0
    Excel Version
    2019
    Quote Originally Posted by p45cal View Post
    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.
    Attached Files Attached Files

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,762
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,115
    Articles
    0
    Excel Version
    365
    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?
    Last edited by p45cal; 2022-02-11 at 01:11 PM.

  8. #8
    Neophyte Saradomin's Avatar
    Join Date
    Feb 2022
    Posts
    4
    Articles
    0
    Excel Version
    2019
    Quote Originally Posted by p45cal View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •