Problem populating table based off two criteria from a different table

[Nade85]

Due to security controls on my system, XL2BB is not an option. Sorry, I really would prefer using that to writing this all out.

Below is the destination table (well, one of them) I'm using to capture employee hours per month per department. This department is "AC" and is one of the few that has people that have moved into and out of this group to/from another department. Basically, I want it to return their hours percentage on this table if they were in this department for that month and on a different departments table if they were over there. I've tried a number of complex equations but I'm not sure if the functionality is there to support what I'm doing. I've structured the reference table to pull in from two other tables so every employee's department and hours percentage are combined into a cell. All of these tables carry over through December and the reference table is displaying correctly.

I've been scratching my head and scouring the forums/web for examples I could cannibalize for about a week. At one point, I got the results over from the reference table into the destination table but it somehow bumped down a row and errored out. That was the closest I got. Can't even tell you how I got that far at this point. Below are the two equations I have been most recently trying to make work. Both return the #Value! error. Portions seem to return what I would expect for that segment of the equations but I cannot seem to get the final answer to come out.

=IF(INDEX(Monthly_Structure[#All],MATCH(IF(Monthly_Structure[Name]=[@Name],Monthly_Structure[March],"False1"),IF(Monthly_Structure[Name]=[@Name],Monthly_Structure[March],"False2"),0),4)=B64&"/*",RIGHT(INDEX(Monthly_Structure[#All],MATCH(IF(Monthly_Structure[Name]=[@Name],Monthly_Structure[March],"False1"),IF(Monthly_Structure[Name]=[@Name],Monthly_Structure[March],"False2"),0),4), LEN(INDEX(Monthly_Structure[#All],MATCH(IF(Monthly_Structure[Name]=[@Name],Monthly_Structure[March],"False1"),IF(Monthly_Structure[Name]=[@Name],Monthly_Structure[March],"False2"),0),4)-SEARCH("/",INDEX(Monthly_Structure[#All],MATCH(IF(Monthly_Structure[Name]=[@Name],Monthly_Structure[March],"False1"),IF(Monthly_Structure[Name]=[@Name],Monthly_Structure[March],"False2"),0),4)))*1))

=VLOOKUP([@Name],Monthly_Structure[#All],IF(COLUMN('Base Data'!BB2)='% Hrs Per Period By Code'!B64&"/*",RIGHT(COLUMN('Base Data'!BB2),LEN(COLUMN('Base Data'!BB2))-FIND("/",COLUMN('Base Data'!BB2))),""),FALSE)

I'm running an index/small array equation to filter the names for the destination table from the reference table to those that have been in the desired department at some point in the year. Equation below:

{=IFERROR(INDEX(Table9[Name],SMALL(IF(Table9[AC]=$B$64,ROW(Table9[Name])-ROW('Base Data'!$AO$3)+1),ROWS('Base Data'!$AO$3:AO6))),"")}

The equation for populating the reference table's monthly cells is below:

=IF(IF(ISBLANK(Pay_Period_Structure[@[1-1]])*ISBLANK(Pay_Period_Structure[@[1-2]]),"",IF(Pay_Period_Structure[@[1-1]]=Pay_Period_Structure[@[1-2]],Pay_Period_Structure[@[1-1]],IF(ISBLANK(Pay_Period_Structure[@[1-1]]),Pay_Period_Structure[@[1-2]],IF(ISBLANK(Pay_Period_Structure[@[1-2]]),Pay_Period_Structure[@[1-1]],Pay_Period_Structure[@[1-2]]))))&"/"&'RAMS Overall'!F5 = "/","", IF(ISBLANK(Pay_Period_Structure[@[1-1]])*ISBLANK(Pay_Period_Structure[@[1-2]]),"",IF(Pay_Period_Structure[@[1-1]]=Pay_Period_Structure[@[1-2]],Pay_Period_Structure[@[1-1]],IF(ISBLANK(Pay_Period_Structure[@[1-1]]),Pay_Period_Structure[@[1-2]],IF(ISBLANK(Pay_Period_Structure[@[1-2]]),Pay_Period_Structure[@[1-1]],Pay_Period_Structure[@[1-2]]))))&"/"&'RAMS Overall'!F5)

Destination Table:

Reference Table:

 

[Nade85]

Apparently I just needed a break. Finally found a workable answer. Probably not optimized but it functions. Hopefully it will provide some insight if anybody else is trying to do anything similar.

Final working equation for the first cell in the January column of the destination table:

=IFERROR(IF(RIGHT(IF(ISNUMBER(FIND($B$64,INDEX(Monthly_Structure[#Data],MATCH([@Name],Monthly_Structure[Name],0),2))),INDEX(Monthly_Structure[#Data],MATCH([@Name],Monthly_Structure[Name],0),2),""), LEN(INDEX(Monthly_Structure[#Data],MATCH([@Name],Monthly_Structure[Name],0),2))-FIND("/",INDEX(Monthly_Structure[#Data],MATCH([@Name],Monthly_Structure[Name],0),2)))*1 <> "", RIGHT(INDEX(Monthly_Structure[#Data],MATCH([@Name],Monthly_Structure[Name],0),2), LEN(INDEX(Monthly_Structure[#Data],MATCH([@Name],Monthly_Structure[Name],0),2))-FIND("/",INDEX(Monthly_Structure[#Data],MATCH([@Name],Monthly_Structure[Name],0),2)))*1,""),"")

I have to change the [column_num] for each different column. Repurposing for the other tables and departments should be simple as I will only need to change the reference cell $B$64 for each additional table and the table names (i.e. table19 through table28). Had to multiply the results by 1 to make the percentage come out right. I imagine it has something to do with the Percentage format for the cell not being able to auto-apply to a number brought over by the equation until there is an actual calculation of sort.

As you can see below, blanks are left when people move in and out of the department despite them having hours elsewhere.

Final Destination table appearance: