Pull info from another sheet based on 2 criteria

CF_Project_Coord206

I'm trying to pull in hours to what I'll call Sheet 1 from Sheet 2. I have flexibility in the way I set up Sheet 2, but I need Sheet 2 to be able to have various employees use the same project number. This would be one option for Sheet 2:

In sheet 1, I will have the project number listed in a column, but will only have the project number listed once in the sheet. I want to put the hours for each project under the correct employee column, such as below.

I'm trying to use the INDEX COLLECT formula and I was hoping to set two criteria. In the above image I only have one criteria set, the project number. I'm not concerned about the invalid value, I know how to fix that. But, when I try to put in the second criteria as who it is assigned to, I get an incorrect argument message. For the image above I was just using the formula:

=INDEX(COLLECT({Sheet 2 Hours}, {Sheet 2 Project Number}, [Project Number]@row), 1)

This doesn't allow me to use a similar formula for the Johnny and Dolly columns.

I tried using =INDEX(COLLECT({Sheet 2 Hours}, {Sheet 2 Project Number}, [Project Number]@row, {Sheet 2 Assigned To}, "Willie", 1)) but that gives me #INCORRECT ARGUMENT.

I first tried VLOOKUP, but that wouldn't let me reference the project number more than once from what I could tell.

Any help is greatly appreciated!

Michelle Choate 2

Your parentheses are in the wrong place. You need to close the collect, then a comma 1 then final close parentheses. So it should look like this:

=INDEX(COLLECT({Sheet 2 Hours}, {Sheet 2 Project Number}, [Project Number]@row, {Sheet 2 Assigned To}, "Willie"), 1)