Index Match for querying salary data over time

Occasional Contributor

I have a dataset with the columns shown below:





As you can see in the sample of the data posted above, some employees have had three or four pay changes (employee #6) while others have had more than that (employee #25). I am ultimately trying to get the data to show the following:

1) Only one line per unique employee ID (rows)

2) One column per unique pay change date




I'm looking for a formula to look up a combo of the employee ID and month end date - if the respective employee had a pay change in that month, I want the number to pull into the cell (if they did not, "n/a" or blank / zero). I was initially thinking index match match but since both the ID # and dates are in columns, that's not working.



1 Reply



Assuming [Pay rate change month] column contains Text values and not real dates formatted as mmmyy, with Power Query then Pivot Table (sample attached)


If [Pay rate change month] column contains real dates formatted as mmmyy a classic Pivot Table will do what you want in a minute (also in attached file)