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?
