+ Reply to Thread
Results 1 to 29 of 29

[Moved to VBA] INDEX MATCH Mutiple worksheet and returns mutiple row

  1. #1
    Registered User
    Join Date
    10-09-2015
    Location
    London
    MS-Off Ver
    2016
    Posts
    19

    [Moved to VBA] INDEX MATCH Mutiple worksheet and returns mutiple row

    Hi,

    Im trying to create a search function for a mutiple worksheet data(same format from A3:C19).
    I.e

    When I input the data i want to search in B3, the formula will search through all the worksheets in Column A and return multiple result of Column C in rows.

    I.e. Search Dept : "A"
    Results
    A 2017
    A 2017
    A 2016
    A 2016
    A 2015
    A 2015

    I have attached my worksheet above for example.

    Below is the formula I was using previously which it only searches from one sheet("2017"), now i need to split it to mutiple (2017,2016,2015 and so on......) sheet.

    =IFERROR(INDEX(all!$A$4:$C$500, SMALL(IF(ISNUMBER((SEARCH($B$3,all!$A$4:$A$500))), ROW(all!$A$4:$C$500)-MIN(ROW(all!$A$4:$C$500))+1, ""),ROWS($B$6:B6)),3),"")


    and if possible, can duplicate results be removed during the search instead of using advanced filter everytime after the search.
    Last edited by joelimzh; 11-20-2017 at 01:33 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    1st, it is almost always better to have all data on the same sheet, that would then avoid this problem

    2nd Have not looked at your file, but I would suggest that instead of having "" as the last argument for the IFERROR, you add the exact same formula, but search 2017. repeat as needed
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-09-2015
    Location
    London
    MS-Off Ver
    2016
    Posts
    19

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    Hi FDibbins,

    Reason why I have to split my data into different sheets instead of consolidating them is that, I have an administrator who is entering all the information required in a yearly sheet.
    Each sheet has about 500 data and to consolidate them into 1 sheet, its going to be a very long sheet - I need to keep 7 years of data.

  4. #4
    Registered User
    Join Date
    10-09-2015
    Location
    London
    MS-Off Ver
    2016
    Posts
    19

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    can anyone help?

  5. #5
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    Can you show what is your expected result and how you wish to achieve that ?
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  6. #6
    Registered User
    Join Date
    10-09-2015
    Location
    London
    MS-Off Ver
    2016
    Posts
    19

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    Hi,

    Using my example,

    Instead of using the search for 1 single sheet "all", I want to search the sheets (2017,2016,2015) instead and achieve the same results.

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    I agree with post #2. If you have 7 years where each year has 500 rows, that's 3500 rows which Excel can handle much easier than splitting it up into separate sheets.

    That being said, something like this (also suggested in post #2) should work:

    B6 =IFERROR(IFERROR(IFERROR(INDEX('2017'!C$4:C$500,MATCH(0,IF('2017'!A$4:A$500=B$3,COUNTIF(B$5:B5,'2017'!C$4:C$500),""),0)),INDEX('2016'!C$4:C$500,MATCH(0,IF('2016'!A$4:A$500=B$3,COUNTIF(B$5:B5,'2016'!C$4:C$500),""),0))),INDEX('2015'!C$4:C$500,MATCH(0,IF('2015'!A$4:A$500=B$3,COUNTIF(B$5:B5,'2015'!C$4:C$500),""),0))),"") Ctrl Shift Enter

    Copy down as far as needed.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    VBA solution;

    Please Login or Register  to view this content.
    Click "RUN" in "SEARCH" tab

  9. #9
    Registered User
    Join Date
    10-09-2015
    Location
    London
    MS-Off Ver
    2016
    Posts
    19

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    Quote Originally Posted by 63falcondude View Post
    I agree with post #2. If you have 7 years where each year has 500 rows, that's 3500 rows which Excel can handle much easier than splitting it up into separate sheets.

    That being said, something like this (also suggested in post #2) should work:

    B6 =IFERROR(IFERROR(IFERROR(INDEX('2017'!C$4:C$500,MATCH(0,IF('2017'!A$4:A$500=B$3,COUNTIF(B$5:B5,'2017'!C$4:C$500),""),0)),INDEX('2016'!C$4:C$500,MATCH(0,IF('2016'!A$4:A$500=B$3,COUNTIF(B$5:B5,'2016'!C$4:C$500),""),0))),INDEX('2015'!C$4:C$500,MATCH(0,IF('2015'!A$4:A$500=B$3,COUNTIF(B$5:B5,'2015'!C$4:C$500),""),0))),"") Ctrl Shift Enter

    Copy down as far as needed.
    Hi, thank you for providing the formula, it will becomes a hassle in future if my worksheet gets longer than it need to. and in actual fact, I have 4 different search criteria which means i need to mutiply the formula you have provided by 4 times for each sheet and 7 sheets means 28 sets of those formula. Nevertheless, I greatly appreciate your help, it will help me in the short term for now.



    Quote Originally Posted by JohnTopley View Post
    VBA solution;

    Please Login or Register  to view this content.
    Click "RUN" in "SEARCH" tab
    Hi John, I'm new to VBA.
    Is it possible to explain a bit on how can I extend your sample to 7 worksheets and if I have more than 1 search criteria - user can search either of those options, or can leave them blank.
    e.g. Search by DEPT, Search by Name, Search by Year. These fields will be optional, user can use either of the field or can input all the fields.

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    If you install PowerQuery add-in for Ex2010 (or suitable to your Excel version) you can use PowerQuery solution:
    (without this add-in solution doesn't work)
    • you don't need "all" tab
    • this is case sensitive so A is doesn't equal a
    • after change value in a search cell - refresh result table
    • no vba, no formula(s)
    Last edited by sandy666; 11-13-2017 at 10:45 PM.

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    The VBA handles as many sheets as you like: assumption is they are named as year numbers i.e. 20xx.

    Re multiple criteria: please post a file showing all possible criteria/selections i.e. multiple criteria e.g. Dept & name

  12. #12
    Registered User
    Join Date
    10-09-2015
    Location
    London
    MS-Off Ver
    2016
    Posts
    19

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    Quote Originally Posted by JohnTopley View Post
    The VBA handles as many sheets as you like: assumption is they are named as year numbers i.e. 20xx.

    Re multiple criteria: please post a file showing all possible criteria/selections i.e. multiple criteria e.g. Dept & name
    Hi John,

    Thank you for replying, I have attached a updated file with what I am really working on minus the exact details. Apology on creating a double work.
    From this file, you can see that there are different search fields i.e. Dept, Name, Course Title, Course Provider & Year.

    The user can search using "Either" field, meaning some fields can be left blank. Or user can complete all the fields to create a exact search.
    i.e. input only 2016 and all 2016 details will be pulled out.
    i.e. input Dept - A, Course Name: Apple etc... it will filter to only show fields with 'A' and 'Apple

    Quote Originally Posted by sandy666 View Post
    If you install PowerQuery add-in for Ex2010 (or suitable to your Excel version) you can use PowerQuery solution:
    (without this add-in solution doesn't work)
    • you don't need "all" tab
    • this is case sensitive so A is doesn't equal a
    • after change value in a search cell - refresh result table
    • no vba, no formula(s)
    Hi Sandy, seems complicated for me to use the power query. I tried googled for additional details but does not seems to work.
    See if you can play around with the new sample.

  13. #13
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    Quote Originally Posted by joelimzh View Post
    Hi Sandy, seems complicated for me to use the power query. I tried googled for additional details but does not seems to work.
    See if you can play around with the new sample.
    Like I said in my previous post you need PowerQuery installed before use example from there.
    I need to know you have PowerQuery - then I can prepare new example for your needs.

  14. #14
    Registered User
    Join Date
    10-09-2015
    Location
    London
    MS-Off Ver
    2016
    Posts
    19

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    Quote Originally Posted by sandy666 View Post
    Like I said in my previous post you need PowerQuery installed before use example from there.
    I need to know you have PowerQuery - then I can prepare new example for your needs.
    Hi Sandy, Yes I am using Excel 2016, which i think is built in- that's what i read from online.

  15. #15
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    So change Excel version in your profile. It's save time and text in posts about add-ins.

    and right, PowerQuery and PowerPivot are built-in in Excel 2016.

  16. #16
    Registered User
    Join Date
    10-09-2015
    Location
    London
    MS-Off Ver
    2016
    Posts
    19

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    Quote Originally Posted by sandy666 View Post
    So change Excel version in your profile. It's save time and text in posts about add-ins.

    and right, PowerQuery and PowerPivot are built-in in Excel 2016.
    My bad, I changed that.

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    The VBA changes are considerable and not quite sure how it will handle multi-criteria.

    I would try the Power Query route :I have not installed it as an add-in to my Excel 2010 [ maybe should!]so I have no experience of using it.

  18. #18
    Registered User
    Join Date
    10-09-2015
    Location
    London
    MS-Off Ver
    2016
    Posts
    19

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    Quote Originally Posted by JohnTopley View Post
    The VBA changes are considerable and not quite sure how it will handle multi-criteria.

    I would try the Power Query route :I have not installed it as an add-in to my Excel 2010 [ maybe should!]so I have no experience of using it.
    Hi John, well I guess doing this search is not worth the trouble of going through such tedious steps.
    I should make all my data into a single spreadsheet I guess.

    Nevertheless, appreciate your assistance. The work you have gave is excellent.

    I shall wait for Sandy and see if the powerquery route will solve my issue, else I will think of alternative work flow to consolidate all data into a single spreadsheet.

  19. #19
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    See attached which searches by SINGLE criteria (but NOT year (as yet!))

    Please Login or Register  to view this content.
    For the multi-criteria I think it will need to use the Filter option (via VBA).

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    Add "Year" option ...

    Please Login or Register  to view this content.
    code in main routine

    Please Login or Register  to view this content.

  21. #21
    Registered User
    Join Date
    10-09-2015
    Location
    London
    MS-Off Ver
    2016
    Posts
    19

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    Some good stuff, but still unable to Multi-Criteria though, and it be better if the search can be "contain" instead of "exact".

    I really do appreciate your hard work and much learning for me on VBA.
    I have sort of consolidated my data into a single sheet. having 35000 records in there now. :D

    Should I move this thread to VBA instead?
    So maybe someone knows how to multi criteria search.
    Last edited by joelimzh; 11-14-2017 at 04:38 AM.

  22. #22
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    There is a problem with mutual exclusion (eg. Dept=A vs Trainee Name=B1) - result will be null
    I can try with quasi-wildcards (real wildcards doesn't work with PQ) and you can type anything what will be contained in any of the column, ie ppl show all records with apple, cpple etc. or men show all records with showmen, x-men etc. or 201 show 2016, 2017 etc but doesn't show 2000.

    VBA will be an option for you or with formula with criteria selected via DataValidation

    It's up to you

    NB. I'm still trying
    Last edited by sandy666; 11-14-2017 at 05:48 AM.

  23. #23
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    Ok, check if it's enough for you.
    • enter string into search cell then refresh query table
    • PQ is case sensitive
    • result will be visible if string from the search cell is contained in query table

    Edit:
    Another option: PQ with PT (v3)
    Last edited by sandy666; 11-14-2017 at 12:24 PM. Reason: v3 added

  24. #24
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    Try

    Please Login or Register  to view this content.

  25. #25
    Registered User
    Join Date
    10-09-2015
    Location
    London
    MS-Off Ver
    2016
    Posts
    19

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    Hi Sandy

    Great work there, however the filters seems troublesome to use as user will need to scroll and find the right choice, thus the field input ones is preferred.
    However beside being case sensitive, it appears that there's no way to drill down to details. i.e. search for 2017 and person name only. The single search option returns all fields that matches.
    I think the VBA option suits better.

    Nevertheless thank you so much, I got to learn a bit on "PowerQuery" which works well for simple search through the whole workbook.



    Hi John,

    The VBA you provided only works for the first 2 search fields. When I enter a 3rd criteria, the VBA does not work anymore.
    When I do single search for Title and Provider - the results returned does not seems right too.

    If the VBA is right, when I do the below search criteria:
    DEPT - A
    Trainee Name - A1
    Course Title - Apple
    Course Provider - A
    Year - 2017

    Results should return:
    A - A1 - A for Apple - A 2017

  26. #26
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    For any YEAR selection, the VBA selects all data for that year so I need to modify it if the YEAR plus other criteria is selected.

  27. #27
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    No problem, have a nice day

  28. #28
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    I cannot get the filter to work consistently so i suggest transfer this to VBA/Macro forum and let the real VBA experts look at it.

  29. #29
    Registered User
    Join Date
    10-09-2015
    Location
    London
    MS-Off Ver
    2016
    Posts
    19

    Re: INDEX MATCH Mutiple worksheet and returns mutiple row

    Hi John,

    Alright, thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] COUNTIF mutiple criteria in a single range, but with mutiple ranges.
    By Janbi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-19-2016, 06:31 AM
  2. [SOLVED] Formula to Index and Match across mutiple worksheets not working
    By Tryin2Excel in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 07-20-2016, 12:15 PM
  3. [SOLVED] Lookup with mutiple criteria - Vlookup, Match, index not working
    By PM1985 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-17-2014, 07:10 AM
  4. Conditional formating, mutiple variables, mutiple sheets
    By Adam_D in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-11-2012, 06:47 AM
  5. Replies: 0
    Last Post: 01-06-2012, 07:09 AM
  6. lookup with mutiple returns
    By mheinmiller in forum Excel General
    Replies: 3
    Last Post: 12-16-2008, 03:58 PM
  7. sum for mutiple returns - formula needed. sumif?
    By mheinmiller in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-11-2008, 06:17 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1