Index/match with multiple match criteria

U

USAOz

New member
Joined
Sep 23, 2017
Messages
16
Reaction score
0
Points
0
Excel Version(s)
2016
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.
 

Attachments

  • Index Match multi worksheet problem.xlsx
    11.1 KB · Views: 13
The attached spreadsheet does not relate to the explanation you provided. Did you attach the wrong workbook?
 
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
 

Attachments

  • Index Match multi worksheet problem.xlsx
    11.1 KB · Views: 11
Last edited:
OK, correct file now uploaded
 
Have you had a chance to view the correct file?
 
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!
 
USAOz said:
Have you had a chance to view the correct file?
Click to expand...


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."
 
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.
 

Attachments

  • Index Problem.xlsx
    39.6 KB · Views: 7
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
 

Attachments

  • Index Problem.xlsx
    40 KB · Views: 14
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)
 
USAOz said:
I wish to sum the total wages for a selected employee in a particular quarter.
Click to expand...
Attached has some formulae on the Wages & Salaries sheet in range D31:G35.
Select your employee ID in cell A32.
Have I guessed right?
 

Attachments

  • ExcelGuru10929Index Problem.xlsx
    40.6 KB · Views: 3
Last edited:
Bob Phillips said:
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)
Click to expand...

To which thread is this related? Your cell references do not match anything in my last file upload.
Also, are the double minuses in your formula correct? I can't work out the logic.
Neverheless, thanks for taking the time to attempt a solution. It is greatly appreciated.
 
The double minus (--) forces what follows to be a numerical value instead of text, so yes, it is correct.
 
USAOz said:
To which thread is this related? Your cell references do not match anything in my last file upload.
Also, are the double minuses in your formula correct? I can't work out the logic.
Neverheless, thanks for taking the time to attempt a solution. It is greatly appreciated.
Click to expand...

When I said Q1, I meant Quarter 1, cell E31, not cell Q1.

It most definitely does match your last upload, C8:C30 is the date which I parse, L8:L309 is the weekly income.

The double unary is to coerce an array of TRUE/FALSE into an array of 1/0 so that the product part of SUMPRODUCT has numbers to work on (a non-matched condition, FALSE, evaluates to 0 and so the product adds in 0).
 
You must log in or register to reply here.
Back
Top