Pull info from another sheet based on 2 criteria

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!

Tags:

Best Answer

Answers

  • Michelle Choate 2
    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)

    Michelle Choate

    michelle.choate@outlook.com

    Always happy to walk through any project you need help with!

  • Thanks for the assistance. When I fixed the formula to this:

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

    I am now getting an #UNPARSEABLE message. I copied the formula I have exactly so that I could make sure there were no mistakes. My sheet name "Formula Test Task List 2" in the above formula is the same as what I call Sheet 2 above. The [Project Number]@row is referencing the cell in Sheet 1 that has the project number.

    Thanks again for your help!

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Answer ✓

    @CF_Project_Coord206, you have an extra close parenthesis after your [Project Number]@row. Remove that and you should be good.

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

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • This worked! Thank you so much @Jason Tarpinian and Michelle Choate!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!