Index Match for querying salary data over time

%3CLINGO-SUB%20id%3D%22lingo-sub-3295825%22%20slang%3D%22en-US%22%3EIndex%20Match%20for%20querying%20salary%20data%20over%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3295825%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20dataset%20with%20the%20columns%20shown%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22egspen2_1-1651017854835.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F367094iFCA8D2208D923B72%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22egspen2_1-1651017854835.png%22%20alt%3D%22egspen2_1-1651017854835.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%20in%20the%20sample%20of%20the%20data%20posted%20above%2C%20some%20employees%20have%20had%20three%20or%20four%20pay%20changes%20(employee%20%236)%20while%20others%20have%20had%20more%20than%20that%20(employee%20%2325).%20I%20am%20ultimately%20trying%20to%20get%20the%20data%20to%20show%20the%20following%3A%3C%2FP%3E%3CP%3E1)%20Only%20one%20line%20per%20unique%20employee%20ID%20(rows)%3C%2FP%3E%3CP%3E2)%20One%20column%20per%20unique%20pay%20change%20date%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22egspen2_2-1651017916266.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F367095iA7DF095FAAF1C424%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22egspen2_2-1651017916266.png%22%20alt%3D%22egspen2_2-1651017916266.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20for%20a%20formula%20to%20look%20up%20a%20combo%20of%20the%20employee%20ID%20and%20month%20end%20date%20-%20if%20the%20respective%20employee%20had%20a%20pay%20change%20in%20that%20month%2C%20I%20want%20the%20number%20to%20pull%20into%20the%20cell%20(if%20they%20did%20not%2C%20%22n%2Fa%22%20or%20blank%20%2F%20zero).%20I%20was%20initially%20thinking%20index%20match%20match%20but%20since%20both%20the%20ID%20%23%20and%20dates%20are%20in%20columns%2C%20that's%20not%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3295825%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3295899%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20for%20querying%20salary%20data%20over%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3295899%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F827443%22%20target%3D%22_blank%22%3E%40egspen2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20%5BPay%20rate%20change%20month%5D%20column%20contains%20Text%20values%20and%20not%20real%20dates%20formatted%20as%20%3CSTRONG%3Emmmyy%3C%2FSTRONG%3E%2C%26nbsp%3Bwith%20Power%20Query%20then%20Pivot%20Table%20(sample%20attached)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%26nbsp%3B%5BPay%20rate%20change%20month%5D%20column%20contains%20real%20dates%20formatted%20as%26nbsp%3B%3CSTRONG%3Emmmyy%3C%2FSTRONG%3E%20a%20classic%20Pivot%20Table%20will%20do%20what%20you%20want%20in%20a%20minute%20(also%20in%20attached%20file)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a dataset with the columns shown below:

 

egspen2_1-1651017854835.png

 

 

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

 

egspen2_2-1651017916266.png

 

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

@egspen2 

 

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)