Index Match with Dates and Other Multiple Criteria

[bcobrien77]

Hello,

I am trying to reconcile some payroll data. Superficially there are many employees that change their position for whatever reason. At any payroll close, I need to know which employees are in which positions. I have a list of employees and a list of potions with their start/end dates.

What I need to do is use formulas to populate the table starting in cell A23 with the correct employee/position combo for the particular date.

I have attached an example with some common issues.

New position has not started and should be ignored for a particular payroll. (Position 20 in Red)
Employee switches positions. (Position 12 in green)

Employee has left before analysis begins (position 21 in yellow)

I would appreciate some pointers.

Thanks in advance.

 

[Hui]

BCObrien

Firstly, Welcome to the Chandoo.org Forums

C2: =IFERROR(MIN(IF(OFFSET($B$27:$U$27,MATCH($B2,$A$28:$A$45,0),,)=$A2,$B$25:$U$25,FALSE)),"-") Ctrl+Shift+Enter
D2: =IFERROR(MAX(IF(OFFSET($B$27:$U$27,MATCH($B2,$A$28:$A$45,0),,)=$A2,$B$25:$U$25,)),"-") Ctrl+Shift+Enter

Copy each cell down

or see attached file:

 

[prasaddn]

Hi Hui,

I believe the OP wants formula to get the position values in payperiod table (rows A28 and down).

Regards,
Prasad DN

 

[prasaddn]

Hi BCObrien,

Use this formula in B28:
=SUMPRODUCT(($A$2:$A$22)*($B$2:$B$22=$A28)*(B$25>=$C$2:$C$22)*(B$25<=$D$2:$D$22))

You can drag to right and down to see results of all the dates and employees.

One catch:
You should not have blank cells in end date column like position 19 and 20.

Regards,
Prasad DN

 

[Hui]

I figured he wanted the top table as some of those values were incorrect?
But on rereading you are correct

 

[bcobrien77]

Thanks so much guys, really appreciate the help!