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?
Best Answer
-
In all likelihood your [pay period helper] column isn't a date field.
Answers
-
In all likelihood your [pay period helper] column isn't a date field.
-
The pay period helper was. The column in the source sheet was not for whatever reason. It is pulling correctly now.
Thanks!
-
Ah, makes sense... pleased you got it sorted.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!