Why do I get #invalid value?

anaMG

Hi everyone!

I am using this formula in the Date column to pull a date if 2 criteria are met (name of the client and the specific module): =INDEX({Date}, MATCH([Column11]3, {Client}, 0), MATCH([Column4]@row, {Module}, 0))

It works in the "Module 1" row, but not below, any idea why? Thanks!

Paul Newcome

You need to specify which item from the list that the COLLECT function pulls together that you want to INDEX. Try putting a comma one in between the two closing parenthesis at the end.

=INDEX(COLLECT(.................), 1)

ker9

@anaMG

See this post

https://community.smartsheet.com/discussion/86176/index-match-using-multiple-criteria

anaMG

Thank you, I tried INDEX(COLLECT, but I get "Incorrect argument set".

This is the formula I tried: =INDEX(COLLECT({Date}, {Client}, [Column11]3, {Module}, [Column4]@row))

Paul Newcome

You need to specify which item from the list that the COLLECT function pulls together that you want to INDEX. Try putting a comma one in between the two closing parenthesis at the end.

=INDEX(COLLECT(.................), 1)

anaMG

Thank you Paul! That did it, I don't really understand why, but it worked!

Thanks!

Paul Newcome

Basically the COLLECT pulls together an array or list from the first range based on the rest of the range/criteria sets.

Then we use the INDEX function to pull from that list. An array built on the back-end such as one built by the COLLECT function will always be in a vertical format, so we use a one in the "row to pull from" section of the INDEX function to tell it to pull the first value from the array.