+ Reply to Thread
Results 1 to 9 of 9

Vlookup, indirect, index, match, countif from multiple sheet & with two criteria

  1. #1
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,892

    Vlookup, indirect, index, match, countif from multiple sheet & with two criteria

    In excel 2007. I have multiple sheets. In those sheet i have in a2, a3, a4 up to a13 column student name.
    In column B1 to AF1 i have dates. This structure in all sheet is same. column B2 : AF13 i enter data.

    Now in summary sheet i have pull those data with specific student name & date. (structure is same as per other all sheet)
    I define all sheet name as : listsheet
    Please note student name from a2:a13 (vertical) & date from b1:af13 (horizontal)
    I am trying below formula, but i can't pull specific data from multiple sheet with two criteria (student name & date is criteria)
    =VLOOKUP($A2,INDIRECT("'"&INDEX(LISTSHEET,MATCH(1,--(COUNTIF(INDIRECT("'"&LISTSHEET&"'!$A$1:$AF$13"),$A2)>0),0))&"'!$A$1:$AF$13"),{2,3},FALSE)
    Last edited by avk; 10-14-2014 at 01:47 AM. Reason: solved

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,256

    Re: Vlookup, indirect, index, match, countif from multiple sheet & with two criteria

    I think it would be easier to offer suggestions if you upload a small (clean) sample of what you are working with, and show samples what your expected outcome would look like.
    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
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,892

    Re: Vlookup, indirect, index, match, countif from multiple sheet & with two criteria

    sample file attach.
    In summary sheet required output.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,256

    Re: Vlookup, indirect, index, match, countif from multiple sheet & with two criteria

    Thanks

    You may need to adjust the ranges here, but it looks like you only have 13 rows? Use this, copied down and across...

    =INDEX(INDIRECT("'"&TEXT(B$1,"MMMYY")&"'!$B$2:$AF$13"),MATCH($A2,INDIRECT("'"&TEXT(B$1,"MMMYY")&"'!$A$2:$A$13"),0),MATCH(B$1,INDIRECT("'"&TEXT(B$1,"MMMYY")&"'!$B$1:$AF$1"),0))

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,163

    Re: Vlookup, indirect, index, match, countif from multiple sheet & with two criteria

    Great job, Ford!! I'm saving this one!

    Regards,
    Dave

    Edit: Oh yes, thanks for posting this one.
    Last edited by FlameRetired; 10-13-2014 at 08:36 PM.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,163

    Re: Vlookup, indirect, index, match, countif from multiple sheet & with two criteria

    Quote Originally Posted by avk View Post
    sample file attach.
    In summary sheet required output.
    Very well thought out workbook. Great job!

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,256

    Re: Vlookup, indirect, index, match, countif from multiple sheet & with two criteria

    Flame, thanks for the kind words and the feedback

    Reps from members not even involved in the thread are especially appreciated

    (Its really not as fancy as it looks though )
    Last edited by FDibbins; 10-13-2014 at 08:44 PM.

  8. #8
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,892

    Re: Vlookup, indirect, index, match, countif from multiple sheet & with two criteria

    Dear FDibbins : Firstly sorry for delay in reply. Actually i log in today morning 11.00 am.
    Yes great job done. I appreciates your efforts. Thanks.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,256

    Re: Vlookup, indirect, index, match, countif from multiple sheet & with two criteria

    @ avk, not a problem - we are all in different time zones, Im just happy we were able to resolve your question for you, and thanks for the feedback

+ 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] Extracting information from multiple tables (Vlookup, Index match, indirect??)
    By vesper007 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-07-2016, 02:36 AM
  2. How to use VLookup or match/index with multiple criteria?
    By awcwa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-22-2013, 02:31 PM
  3. vlookup? match? index? MULTIPLE criteria for vlookup search problem....
    By aborg88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-11-2013, 09:56 AM
  4. [SOLVED] Index, match, vlookup with multiple criteria
    By saniamarco in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-29-2012, 07:16 PM
  5. [SOLVED] VLOOKUP (multiple criteria) vs. INDEX and MATCH
    By B-dub in forum Excel General
    Replies: 3
    Last Post: 04-13-2012, 06:23 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