Best approach to creating a formula(?) to loop through monthly data, if all employees have different start dates

[ab4100]

Hello,

I'm currently working on a project with employee data and need to create a table that displays each employee's score for 5 different KPIs during each month of their employment. I have a huge table with all the relevant data and was trying to create a pivot table or write a formula that will display these metrics in an easy-to-understand format. The data covers a 36-month period and all of the employees were hired during this period, so they all have different start dates - I need to figure out a pivot table/formula that will display the employees' data for their first month of work, regardless of which specific month was their first. Then, I need this formula/pivot table to show me each employees' performance data for their 2nd/3rd/4th/etc. month of work, all the way up to 36 months.

Right now, I'm using an INDEX MATCH formula to help me find the performance data for month 1 - I used the employee name, KPI metric, and start date to find the matching result in my data table. While this has worked for finding the stats for their first month, I have no clue how to re-write the formula so it will find a match for anything beyond month 1. Is there a way to add a loop to the INDEX MATCH formula, so I can tell the formula to loop until it finds a 2nd/3rd/etc. matching result?

I've never made a pivot table before and tried to make one for this issue but got stuck and wasn't sure how to lay it out. Essentially, my data covers the period of Jan. 2020 to Dec. 2022 (36 months). I have a list of employees that were hired during this period, as well as their performance metrics. I need to create a table/formula that will display their performance during month 1, regardless of start date (so for someone hired in June 2020 - month 1 will be June 2020, month 2 will be July 2020, and so on).

I've attached a screenshot of what I've got so far - the employee number, start date, and INDEX MATCH used to find data for Month 1 (I've also selected cell T6 to show the formula used for the INDEX MATCH). I'd appreciate any insight/advice/opinions on how to continue this work. Thanks

 

[ab4100]

I wasn't able to attach a screenshot - the formula I'm using is:

=INDEX('KPI Data (Canada CCRs)'!$I$2:$I$5691,MATCH(1,(I5='KPI Data (Canada CCRs)'!$A$2:$A$5691)*(K5='KPI Data (Canada CCRs)'!$H$2:$H$5691)*($T$4='KPI Data (Canada CCRs)'!$G$2:$G$5691),0))

^ I2:I5691 is the column in my data pull containing the KPI metrics I need, I5 contains the employee name, K5 contains their start date, and T4 is the title of the specific KPI metric.