How do I pull in a value using 2 criteria?

Jess A

I want to use Project Name and Resource name as a criteria to pull hours worked into a different sheet. Please help.

Gillian C

Hi @Jess A

Glad it helped, you could use IFERROR to remove the #invalidvalue? https://help.smartsheet.com/function/iferror

So

=IFERROR(Your formula, "")

will replace the #invalidvalue with a blank. If you want a 0 then add 0 where the "" are or indeed any other text inbetween "".

Gillian C

Hi @Jess A,

Assuming that you have two columns in the sheet you are pulling the information into with Project Name and Resource Name then you could use Index Collect https://help.smartsheet.com/function/index

Assuming that [Project] and [Resource] are the columns that you are matching the info in the sheet you are pulling the Hourse Worked into, then:

=INDEX(COLLECT({Hours Worked},{Project Name},[Project]@row, {Resource Name}, [Resource]@row),0)

When you are creating the above formula don't manually insert the {} values you need to create cross sheet references within the formula https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Hope this helps

Will Jeffords

hi @Jess A, what’s the format of the destination sheet(s)? Would be helpful to describe your workflow case a bit more. You might benefit from strategic use of Reports depending on your business goals here. Would love to help once I understand your need a bit more.

Best,

Will

Jess A

@Gillian C

Thank you very much! The formula worked when I changed the 0 to a 1.

Follow up questions, some of the projects are not in my reference sheet, so I am getting an #invalidvalue. Is there a way to either return a 0 or remove these? I applied the formula to my entire column.

Gillian C

Hi @Jess A

Glad it helped, you could use IFERROR to remove the #invalidvalue? https://help.smartsheet.com/function/iferror

So

=IFERROR(Your formula, "")

will replace the #invalidvalue with a blank. If you want a 0 then add 0 where the "" are or indeed any other text inbetween "".

Jess A

@Gillian C Thank you so much! You are a life saver!