Replace Index Match with VBA - Multiple Criteria - Thousands of rows and columns

Hello everyone. I have been a member of this site for almost 2 years and have learned a ton! Unfortunately I am venturing into VBA and have not found a solution for what I want.
I have an excel sheet I use to track and employees absence. I will explain what I currently do so you have an idea.
On the Month tab ie JAN, I paste in information from a report, each day I paste on the next empty line the new days absenteeism report and do that each day.
I have created code on the monthly tab that converts the pasted date into something I can index/match and paste the results into the EMER tab which is like a 365 day calendar so we can track Absenteeism and look for patterns.

What I currently have works well, and does exactly what I want, but with 1500 rows and 366 columns , but it is 550K cells filled with code which is slow.

I currently use this code: =IFERROR(INDEX(JAN!$J$1:$J$3000,MATCH(1,(JAN!$G$1:$G$3000=Q$1)*(JAN!$A$1:$A$3000=$B10),0)),"") entered with CTRL+ALT+Enter. into 550K cells in the EMER tab.
The code is adjusted 12 times, once for each month.
The above code is easy to enter as I can drag across and down, but it needs to be different for each month and the Info I paste for each month is on a different tab.

I will explain the code above.
INDEX(JAN!$J$1:$J$3000 - This is the information I want pasted in each cell ie "E", "T", "NC", and others if I add
MATCH(1,(JAN!$G$1:$G$3000=Q$1) - This matches the date in JAN $G$1:$G$3000 with the date in the first row of each column IE: EMER $Q$1
*(JAN!$A$1:$A$3000=$B10) - This match the employee name in JAN A1:A3000 with the name in EMER $B$10:$B$1510


I would like to be able to do this with VBA for many reasons including, size, speed and ease of use and updating requirements as they change.

I have attached an extremely small version of my tracker, and changed the names etc as this is a sensitive document.

I appreciate any and all assistance

Yes, I have played with it. Unfortunately, the presentation is important, as we have 30 managers who look at this sheet daily and it is updated daily by myself, but could move to someone else.
Each month needs to be separated, and each instance of an absent an what it was for needs to stand out so we can see patterns immediately. The employee list changes daily as do the absences.

We are set on the design, unless you can show a pivot table that updates how I have it now, and retains the visual style. Even if I were to use a Pivot table, I still need to lookup each individual absence for every day of the year for up to 1500 employees which gets posted into the monthly tabs, which already has codes in them to make the data compatible with the Vlookups and the information we want to show.

Marvin, I am afraid I cannot provide the live document...legally. It contains real people information.
I did go look at the pivot tables again including the links you provided. If I was looking to for a report they would be good, but we need something straight forward a visually shows each day and each employee.

I am getting a little confused as even if I went to a pivot table, I would still need to have a table filled with data which still needs to be entered the same way I do it now.

How will a pivot table make entering the data any quicker?
What I am looking for is a quicker was to fill the table with data. When took this document over they were entering all this data individually employee by employee at times taking hours. I automated it so I just need to paste in our absenteeism report and everything auto populates. I thought finding a VBA solution would speed things up by not having half a million lines of code running every time I paste something.

I have attached a copy with more data so you can see what I mean about patterns etc. Most of the people using this document come to look at for info, not to analyze it. Seeing the pretty colors and patterns is what helps, not the numbers themselves, although they do need those as well.

What I am looking for is a way to enter the code I need to put in each little cell as VBA instead of array formulas.

Thanks GC Excel, I looked at it and I think I understand what you have done, but I am not actually seeing the "E", "T" or "NC" showing up on the EMER tab.
When I look at the code, I can see you referenced columns 1, 7 and 10, which is the Name, Date and Status (status is what should show on the EMER tab). I understand I will need to make one for each month, I can do that.
On the "write back to sheet" portion of the code I can see you are using lines 23-32, which you added names to. This part confuses me a bit, but I think I can see what it is doing.

Unfortunately, I do not see it writing back to the EMER tab.

I "think" I ran the Local Variable window, which I think is called Watch in 2013. When I tried the first time, there was no plus"
Expression: ar
Value:Empty
Type: Variant/Empty
Context: Module1.Compile

I then tried adding headers and of course the plus sign showed and each ar had 3K lines, it showed the header on the first line, and for the rest of the rows, columns A,B,C,D,E,F (1-6) were empty, column G (7) showed a date from the formula there and columns H,I,J (8-10) showed "".

When I added the header (which I do not use, all data should go into A1:F1 and down, the Run-time 13 error went away, but the Runtime Error 457 returned.

I really want to thank you for taking the time with me here, I admit I am brand new to VBA, just self learning.

I have reverted back to the original.

I have currently added a fictitious name to each month and that fixed the error. When I remove the fictitious name I do not get an error on the line you proposed, I only get one there if there is a duplicate name with the same date.
I still only get the error For i = 1 To UBound(ar, 1) when the first line is empty, and of course that is the Runtime error 13, type mismatch error.
When I hover over it:
the i = 2977, UBound = UBound(ar,1)=<Type mismatch> , ar = Empty

Does that help any?

I wish I could Unfortunately it is filled with real information of employees and personal information. I cannot share it for ethical and legal reasons.
Once I remove the 700 employees the document is again like the one I originally posted which I know does not show the proper information.

I will be able to make this work and I thank you for all your assistance.
I do have one last question though.
The document size is currently 1 MB smaller, but it still takes forever to run the macro, is there any way to not have it run through every cell each time and only update if there is new data added?
There will be times when I re-enter all the data for a given month, however that is not often.
At this point the 550K lines of code was faster, I assume that will not be the case later in the year as more info is added.