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!

Find more posts tagged with

Formulas

Accepted answers

Jason Tarpinian

@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)

All comments

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)

CF_Project_Coord206

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

@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)

CF_Project_Coord206

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.