+ Reply to Thread
Results 1 to 4 of 4

Two criteria Index Match on multiple sheets returning multiple values

  1. #1
    Registered User
    Join Date
    03-14-2014
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2007
    Posts
    10

    Two criteria Index Match on multiple sheets returning multiple values

    Hello,

    I am new to Excel Forum and I am not very advanced in Excel. I hope someone can help me with this.

    I am building a payroll workbook and I need to build a sheet that will allow me to choose an employee name and a date. The multiple values returning are the details of this particular employee and week of work. It could return multiple lines, as one employee can have two entries relating to the same week.

    I found a formula that uses INDEX and MATCH which is giving good results, but my workbook is on multiple sheets (one sheet per month). I don't know how to build a formula that can look into all 12 sheets and return the appropriate data.

    I have built a workbook example : Test formula on payroll.xlsx
    The example is a simpler version with only 3 months. The "Payslip" sheet is where I need to build the formula.

    Thanks for your help.

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Two criteria Index Match on multiple sheets returning multiple values

    Hello and Welcome to the forum !
    First change your sheet name to complete month name i.e. Januray, February, March and so on then in paysilp sheet c14 copy paste below
    then hold control and shift together and Hit enter to make it array formula ! then drag formula to right !


    =IFERROR(INDEX(INDIRECT("'"&TEXT($A$7,"MMMM")&"'!$A$2:$G$100"),MATCH($A$3&$A$7,INDIRECT("'"&TEXT($A$7,"MMMM")&"'!$A$2:$A$100")&INDIRECT("'"&TEXT($A$7,"MMMM")&"'!$B$2:$B$100"),0),COLUMN(C3)),"Not Available")
    Attached Files Attached Files
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Registered User
    Join Date
    03-14-2014
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Two criteria Index Match on multiple sheets returning multiple values

    Hi hemesh,

    Thanks for your reply. Very interesting formula. It didnt do exactly what I need, because I need to return multiple values. But I took the following formula:

    Please Login or Register  to view this content.
    and I added your suggestion for the ranges
    Please Login or Register  to view this content.
    And it works great! Thanks.

    I have another question, if I may. What if I want to do the same thing but only with one criteria that would return results that are on all the sheets. Per example, a table where we can choose one employee and get all the results for this employee trough out the year.

    ps: i've tried to translate everything, but my sheets and formulas are in french on my computer. sorry if it make some confusion.

    Thanks for your help.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-14-2014
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Two criteria Index Match on multiple sheets returning multiple values

    Hello all,

    I have this other question explained above. Maybe it is best to create a new post. So I'll do that.

    Thanks for your help hemesh.

+ 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. Vlookup (or index/match) with multiple criteria over multiple sheets
    By Groovicles in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2013, 01:56 PM
  2. [SOLVED] Index match with multiple criteria and returning last value
    By L.Steele in forum Excel General
    Replies: 2
    Last Post: 05-30-2012, 02:27 PM
  3. Index Match Multiple Criteria Multiple Sheets
    By sctraffic in forum Excel General
    Replies: 10
    Last Post: 07-21-2011, 03:47 PM
  4. Replies: 8
    Last Post: 06-20-2011, 05:54 PM
  5. Returning MULTIPLE values with Index and Match
    By Fly in forum Excel General
    Replies: 1
    Last Post: 06-01-2006, 12:55 PM

Tags for this Thread

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