Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Index/match with multiple match criteria

  1. #1
    Seeker USAOz's Avatar
    Join Date
    Sep 2017
    Posts
    12
    Articles
    0
    Excel Version
    2016

    Index/match with multiple match criteria



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

    I have a wages spreadsheet (sample attached) which has columns for Employee ID, quarter in which wages were earned and wages earned

    I have a summary table below the date intoi which I wish to sum the total wages for a selected employee in a particular quarter.

    My underrstanding of thye problem is that I wouyld need to index the wages , match the employee id in the sumary table AND the quarter in the summary table to total the employee's wages in each quarter.

    I am using ther formula
    =IF(ISBLANK($A27),0,IFERROR(INDEX($C$4:$C$16,MATCH($A27,$A$4:$A$16,0)),""))

    but that not only does NOT correctly sum each employee for Quarter 1 but fails to sum Q2,3,and 4.

    The atached sample shows the Employee ID (column A), Earniong Quarter (Column B) and wages (Column C).

    Below that data is an incorrect summary table and a table showing my expected values.

    Any help is solving this?

    Thanks.
    Attached Files Attached Files

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    283
    Articles
    0
    Excel Version
    2019
    The attached spreadsheet does not relate to the explanation you provided. Did you attach the wrong workbook?

  3. #3
    Seeker USAOz's Avatar
    Join Date
    Sep 2017
    Posts
    12
    Articles
    0
    Excel Version
    2016
    Hmmmmm - how can that happen? It was the only file on my desktop but, you ARE correct. I will try to upload the correct one again. Sorry
    Attached Files Attached Files
    Last edited by USAOz; 2020-11-17 at 01:22 AM.

  4. #4
    Seeker USAOz's Avatar
    Join Date
    Sep 2017
    Posts
    12
    Articles
    0
    Excel Version
    2016
    OK, correct file now uploaded

  5. #5
    Seeker USAOz's Avatar
    Join Date
    Sep 2017
    Posts
    12
    Articles
    0
    Excel Version
    2016
    Have you had a chance to view the correct file?

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,521
    Articles
    0
    Excel Version
    Office 365 Subscription
    Your sample results data is all wrong - there is only one ID match!

    This will return proper matches:

    =INDEX('Employees & Contractors'!$C$3:$I$6,MATCH('Wages & Salaries'!$A4,'Employees & Contractors'!$B$3:$B$6,0),MATCH('Wages & Salaries'!B$3,'Employees & Contractors'!$C$2:$I$2,0))

    If the results you show are what you want, you have a lot of explaining to do!
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,843
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by USAOz View Post
    Have you had a chance to view the correct file?

    I see no difference between the files - perhaps you updated them both?
    I don't see quarters data (no Earniong Quarter header), no date data and no "Below that data is an incorrect summary table and a table showing my expected values."

  8. #8
    Seeker USAOz's Avatar
    Join Date
    Sep 2017
    Posts
    12
    Articles
    0
    Excel Version
    2016
    I can see BOTH uploaded files on my emnd and they are definitely different! The correct file that matches my problem description is the latest upload and I will upload it again.
    Attached Files Attached Files

  9. #9
    Seeker USAOz's Avatar
    Join Date
    Sep 2017
    Posts
    12
    Articles
    0
    Excel Version
    2016
    OK, just a heads up, I did a tweak on the spreadsheet I just uploaded that should make things easier to understand so PLEASE view the attached REVISED spreadsheet. Sorry for the inconvenience. Basically, I still can't get INDEX/MATCH TO POPULATE THE SUMMARY TABLE on the Wages & Salaries worksheet
    Attached Files Attached Files

  10. #10
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,835
    Articles
    0
    Excel Version
    O365
    Here is a formula to give you weekly income for Q1, the rest should be easily figured out

    =SUMPRODUCT(--(ROUNDUP(MONTH($C$8:$C$30)/3,0)=E$30),--($C$8:$C$30<>""),$L$8:$L$30)

Page 1 of 2 1 2 LastLast

Posting Permissions

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