Index match issue

[KDavidP1987]

Greetings Excel Wizards,

CONTEXT:

I'm having an issue that I can't seem to resolve, and I don't know why. I have a workbook, Agent Performance Report, which includes (named) tables for two series of data (Metrics) on agent performance, and a sheet which combines that data together into a singular monthly report for each agent using Index Match Formulas.

The sheets from the workbook are as follows: Coaching Dashboard, ODF_MonthlyData, FootPrints_MonthlyData, EmployeeRoles

The Coaching dashboard has a table on it which includes the following columns (named): {Year, Month, Date Validity, Calls Answered, AHT, Bumped Calls, NR Time, Total Tickets Worked, Phone Tickets Worked, Phone Tickets FCR Eligible, Phone FCR}

The following columns in that table represent data from the ODF_MonthlyData sheet: {Calls Answered, AHT, Bumped Calls, NR Time}

The following columns in that table represent data from the FootPrints_MonthlyData sheet: {Total Tickets Worked, Phone Tickets Worked, Phone Tickets FCR Eligible, Phone FCR}​

Year spans 2018 and 2019, Month column spans January through December of each year, used for Index Match. Date Validity is a column which includes a formula that compares the month/date with the agents start date, so the table doesn't show old invalid data (from previous users, user IDs are recycled).

THE ISSUE:

The index match formulas for the ODF metrics work, the formula is as follows:

=IFERROR(IF([@[Date Validity]]="Valid",INDEX(ODF_MonthlyData!$1:$1048576, MATCH(1,(VLOOKUP(AgentName,EmployeeRoles[[FootPrints Name]:[OD Focus Name]],2,FALSE)=ODF_MonthlyData!$C:$C)*([@Month]=ODF_MonthlyData!$B:$B)*([@Year]=ODF_MonthlyData!$A:$A),0), MATCH(G$13,ODFdata[#Headers],0)),""),"")

The INDEX MATCH formulas for the Footprints data, which mostly used structured references (Table name, Column name, etc), doesn't work.

Note: I am 100% using CTRL + SHIFT + ENTER for the array, can confirm that isn't an issue. They result in a blank cell, because of the IFERROR statement, otherwise N/A# for failed INDEX MATCH.

Attempt 1:

=IFERROR(IF([@[Date Validity]]="Valid",INDEX(FootPrintsData[#Data], MATCH(1,(AgentName=FootPrintsData[[#Data],[Agent]])*([@Month]=FootPrintsData[[#Data],[Month]])*([@Year]=FootPrintsData[[#Data],[Year]]),0), MATCH(K$13,FootPrintsData[#Headers],0)),""),"")

Note on Attempt: I can confirm that the Date Validity in these rows is returning TRUE, so it's trying to process the INDEX MATCH but failing. If I remove the IFERROR statement then it returns N/A#, the common return for a failed INDEX MATCH.

Attempt 2:

=IFERROR(IF([@[Date Validity]]="Valid",INDEX(FootPrintsData, MATCH(1,(AgentName=FootPrintsData[Agent])*([@Month]=FootPrintsData[Month])*([@Year]=FootPrintsData[Year]),0), MATCH(M$13,FootPrintsData[#Headers],0)),""),"")

Attempt 3:

=IFERROR(IF([@[Date Validity]]="Valid",INDEX(FootPrintsData[Phone Tickets Worked], MATCH(1,(AgentName=FootPrints_MonthlyData!$C:$C)*([@Month]=FootPrints_MonthlyData!$B:$B)*([@Year]=FootPrints_MonthlyData!$A:$A),0)),""),"")

Note on Attempt: Removed the second part of the match formula (to specify column). Made the INDEX formula focus on the column I need.

My Hypothesis: INDEX MATCH isn't working with structured references???

Can someone confirm if there is a way to fix this? The data I'm referencing changes/updates so I don't want to use hard coded cell references.

 

[KDavidP1987]

I think I found the issue, but it presents another problem. When I go to paste in the data, the year column has a green arrow asking if I would like to "convert it to a number". I changed one to number, on the agent selected in the drop down box, and his metrics were properly reflected by the first and third INDEX MATCH formulas.

However, the year in the Coaching Dashboard is formatted as GENERAL, so I don't see why this is causing an issue. Also, if I try selecting the entire column and changing the format to number or to general the green indicator doesn't go away and the metrics aren't reflected unless I change them on a cell by cell basis.

Anyone know what is causing this, is it some sort of glitch?

Sincerely,
Kris