Why is Index Collect formula returning Invalid Value error?

I am trying to pull a project number from a source sheet into my current sheet based on the employee number and date column matching on both the current sheet and source sheet. Here's my current formula:

=INDEX(COLLECT({Project # - MP Proj}, {Employee # - MP Proj}, [Employee ID]@row, {Week Start Date - MP Proj}, [Pay Period Helper]@row), 1)

This returns the #INVALID VALUE error.

The formula works as such: =INDEX(COLLECT({Project # - MP Proj}, {Employee # - MP Proj}, [Employee ID]@row), 1)

However, this only pulls the first value in which the employee #s match and does not account for the dates. Looking at the source sheet and current sheet, there are rows in which both the employee # and week start date show the exact same value as on my current sheet.

Any ideas what might be going on here, and how to fix it?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!