INDEX COLLECT, how to collect multiple values in a range when the criteria matches

I am pulling employee hours from a timesheet to a billing sheet. The billing sheet has a column that pulls the employee name from the timesheet based on the date, project number and cost code. I copy and paste the transactions into the billing sheet

=IFERROR(INDEX(COLLECT({Timesheet | Employee Name}, {Timesheet | Project}, [Project Number]@row, {Timesheet | Cost Code}, [Final Cost Code]@row, {Timesheet | Date}, Date@row), 1), "")

There's a few issues I'm running into: Sometimes two employees will work on the same project on the same date under the same cost code. So the formula isn't sophisticated enough to identify two values in the range. And each employee needs it's own row as they all have different labor rates.

Is it possible to solve both of these issues?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!