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

INDEX & MATCH using multiple criteria

Brent Guhl

Jan 5, 2022

Hi and thank you!

I'm using Smartsheet to track and complete medical cases. I'm using two sheets, one patient-facing that I've called the Intake sheet, and one for me, which I call the Tracking sheet. I'm using INDEX and MATCH to copy basic demographic data from the intake sheet to the tracking sheet. Since the same person can have more than one case, the one field that would be different and desired is the date of the case (for my purposes, multiple cases on the same date are treated as the same case). I'm using email as the unique identifier of the person, and would like to use the date of the case as the case identifier for each case that needs to be tracked. I've coded a flag on the intake sheet to let me know when there are duplicates, and I bring that result onto the tracking sheet using INDEX and MATCH. In the case of duplicates (only the date field of the case matters), I want to bring only the most recent case date forward onto the tracking sheet.

I have the INDEX and MATCH working for the other fields, but need to compare dates to get the most recent case.

=IFERROR(INDEX({Intake Date of Exposure}, MATCH([Email]@row, {Email}, 0)), "Email Not on Intake")

---

View Post

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.