Hey everyone,

Been fighting this for a day figured I would check with the experts. I have a pivot table that has my project codes in the left column (A), my employee names across the top (Row 2), and the sum of hours worked contained within the table. I have a data validation field elsewhere with a project list and a ton of related formulas providing analysis on the selected project.

I went to this website and took the formula there to return all employee hour totals against the selected project perfectly. My project selection cell is in A79 and the first project is on A3 (actual pivot is A1:P74) but the P column is grand total and row 74 is total per employee. Employee names are header columns from B2:P2.

=IFERROR(INDEX($B$3:$P$73,SMALL(IF(($A$3:$A$73=$A$79)*($B$3:$P$73<>""),MATCH(ROW($B$3:$P$73),ROW($B$3:$P$73)),""),ROW(A1)),1/(SMALL(IF(($A$3:$A$73=$A$79)*($B$3:$P$73<>""),MATCH(ROW($B$3:$P$73),ROW($B$3:$P$73))+1/MATCH(COLUMN($B$3:$P$73),COLUMN($B$3:$P$73)),""),ROW(A1))-SMALL(IF(($A$3:$A$73=$A$79)*($B$3:$P$73<>""),MATCH(ROW($B$3:$P$73),ROW($B$3:$P$73)),""),ROW(A1)))),"")

I have this formula copied down into 20 rows to accommodate various total #'s of employees on a specific job. I need the actual employee name however, not the hour totals and that's where I'm getting stuck. I'm using this right now:


=INDEX($B$2:$T$2,MAX(IF($B$3:$T$73=INSERT PREVIOUS FORMULA HERE,COLUMN($B$3:$T$73)-COLUMN($B$2)+1,"N/A")))

It's hit or miss. I always have the correct amount of entries for employees per project but whether its the right employee is in the air. I have the range extending to T73 because the max function will return all employee names with hours and then the last employee's name indefinitely until the 20 rows I have setup are all filled. In the t column I have .0001 and left the column header blank. Regardless, I feel like there is a much easier way to use the first formula to return the same column header. The header will always be in row 2 so I just need Row2, Col(# that the hours are in).

I'm trying not to post the workbook because of the sensitive nature of the data but if I have to I'll scrub down and get a truncated version up.

Thanks in advance