INDEX COLLECT give #INVALID VALUE error but INDEX MATCH does not

heyjay

INDEX COLLECT gives me an #INVALID VALUE error

=INDEX(COLLECT({event-25}, {date-25}, Day@row , {ID-25}, [Employee ID]@row ), 1)

=========

But using the same reference for INDEX MATCH does not

=INDEX({event-25}, MATCH(Day@row , {date-25}, 0))

or this

=INDEX({event-25}, MATCH([Employee ID]@row , {id-25}, 0))

=========

I need to match what Event does the employee have on the specific date on my current sheet. I am using a calendar sheet as my source sheet. Date column has been formatted as date on both sheets.

Current Sheet

Source Sheet

jmyzk_cloudsmart_jp

Hey @heyjay

If your Employee ID is only like 0914234, having 0 (zero) at the top, adding = in front of the [Team Member ID]@row will remove the error.

I guess you get the error as Smartsheet's COLLCET function treats [Team Member ID]@row's value as a number when comparing with the range {ID-25}.

[Event Type]
=INDEX(COLLECT({event-25}, {date-25}, Date@row, {ID-25}, =[Team Member ID]@row), 1)

As the 6th row in the image below shows, your formula works if both the range and the [Team Member ID]@row's value is a number.

https://app.smartsheet.com/b/publish?EQBCT=a72015c4417242fc8c55740e08147bba

If you need to cope with both the number and text format of the ID, you can use a formula like this, which forces both to be text.

[Event Type 2]
=INDEX(COLLECT({event-25}, {date-25}, Date@row, {ID-25}, @cell + "" = [Team Member ID]@row), 1)

https://app.smartsheet.com/b/publish?EQBCT=e37d156255dc405ab9a1dfecbc9c6261