Lookup multiple values with multiple criteria, fill horizontally by header category

[ophizer]

Hello friends, a long time lurker-first time poster. This place has saved my *** on multiple occasions. Unfortunately, I have now spent days looking for a solution without much luck and felt compelled to ask my first question.

The problem at hand is this. I have a list of employees (by ID) and series of events associated with those employees (going horizontally). Some folks have multiple events taking place during different times (hence the duplicate empl IDs). Below I have highlighted the duplicate rows. Employee ID 701, had a manager change (XFRMng) on 7/20/17 and was promoted (PROM) on 8/17/17. Numbers 47 and 46 that you see in the cells are row counts from the dataset (this is a summary pivot). Soooooooo, how do I update a different sheet (to create an employee "history") by matching the Event categories on top, by capturing multiple dates and then returning them in the same row.





Here's an image how it should look




Many thanks in advance.

 

[SpillerBD]

Your first image, is that a Pivot Table?
If so what is the layout of its source data?

 

[ophizer]

It is a crazy HR report without any regard for data integrity or cleanliness. So I am setting up a one to many relationship with reference tables that summarize "event categories" by User ID and Date Modified in a pivot which I then want to use to populate my model. See highlighted in yellow

[h=2]https://1drv.ms/x/s!<wbr>AjWNuqhgu9nvgVHHloPN<wbr>fPwtmqDt[/h]

 

[ophizer]

sorry probably should have included the "events" reference table

https://1drv.ms/x/s!AjWNuqhgu9nvgVOd9yYYQfFy_1LX

 

[ophizer]

using the "small" function I managed to identify the row #, but I can't figure out how to specify which column to reference, in order to get the date. Everything that I've read on the subject, only show how to do by using "columns" function but they stop short of showing how to get an exact match.