Why is Index Collect formula returning Invalid Value error?

brianschmidt

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?

BullandKhmer

In all likelihood your [pay period helper] column isn't a date field.

brianschmidt

https://community.smartsheet.com/discussion/comment/373793#Comment_373793

The pay period helper was. The column in the source sheet was not for whatever reason. It is pulling correctly now.

Thanks!

BullandKhmer

https://community.smartsheet.com/discussion/comment/373847#Comment_373847

Ah, makes sense... pleased you got it sorted.