lookup returning multiple values - SO CLOSE!

[chrklein26]

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

[FDibbins]

Hi and welcome to the forum

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[chrklein26]

Thanks for the reply.

I've attached a file with generic information to explain what I'm doing/trying for.

sheet1 is my current file. My pivot table pulls from the timesheet table to populate projects and hour totals by employees. A79 has a dropdown list to select projects and my formula for returning all employees is in range(A91:A113). The table always returns the correct # of employees per job but not always the correct employee - I believe it's due to the max function in the formula.

sheet2 is the same page with the max formula removed. The returned values are the total hours worked per employee per project, not the names, but the returned values are always correct.

sheet3 is my intended goal. The correct employee names are returned for the selected project (i've manually entered them as an example). I'd like sheet1 to display the same values in range (A91:A113) that are shown in sheet3 and to do so correctly regardless of project selection.

I know enough VBA that I think I could hardcode the process but it would be time intensive and much less effective. I'm changing jobs soon and want to leave my previous boss with a dynamic tool that will automatically update to match the data-sets that are being pulled from our database. A quick question regarding that as well - my formula has all ranges referenced in absolute values. I was planning on changing those to named data tables/arrays/ranges after I got everything working correctly. Will this enable the formula to retain functionality after the pivot has been refreshed and new projects/employees are added changing the total rows/columns?

Thanks so much for the help!

[chrklein26]

Scanned forum rules, hope this is ok...

*bump*

[icestationzbra]

hi there,

use this ARRAY formula in cell A91 on the Intended Output tab, then drag-fill down:

Please Login or Register  to view this content.

using Excel Smart Table is not always helpful, yours is one of those cases. it does not allow for the usual anchoring and stuff, when called in formulae...

UPDATE:

please use the following in your actual file (and i suggest that you remove it from the forum now because it seems to have real data):

Please Login or Register  to view this content.

[chrklein26]

You sir... are a genius

[chrklein26]

Update: There is one oddity with this after I refreshed my tables to get the actual project names and employee names. Each project selection has the correct employees listed everytime. However, it also lists one additional employee who didn't work on the project. This doesnt impact the rest of the spreadsheet because they have no hours and don't factor into the formula's. I've used conditional formatting as a quick fix to color the employee name font the same color as the background if they have no hours in the next column but I'm curious why this happened.

Employee names are formatted like this

cklein
gklein
mklein

Projects are formatted like this - aaa.aaa.aaa.### with a = letter and # = number

Example - exl.for.kup.001

Some projects may have an additional letter in one of the sections but basically is as the example. Any idea whats up?

Thanks again so much!!!!


Update2: And on further check some projects are returning more than just one extra employee. (I've figured out that this happens when the user_name field isn't sorted to the specified match principle) Still having the single phantom entry occurring after the correct names are pulled. I think something is happening that is shifting one of the array's off by one row. The phantom employee is always the employee in the next row AFTER a row that has the correct project in it. Everytime. I also flipped it around to pull projects by employee and same thing. The phantom return is a project one row after the last correct return in the data set.

[chrklein26]

Here is an updated example file. I've changed the information but left the exact format that is used. I've highlighted the cells that hold the lookup value light blue and the column in the tables that have the index formula.

Thanks again.

Edit: File Removed