INDEX(COLLECT)?

ChloeSmith

Hello,

I have a question that is likely fairly simple, but I can't figure out.

I need to build a formula that will look at one sheet (three separate columns) and return a date in a separate sheet when the three columns match a certain set of requirements.

For the first requirement, I need it to look at sheet two, column one and match data in sheet one, column one.

For the second requirement, I need it to match a specific word that would appear in a column on sheet two.

For the third requirement, I need it to check to see if a date is less than or equal to 90 days in the past.

Here's what I was trying:

=INDEX({Sheet 2 Range 3}, COLLECT({Sheet 2 Range 1}, {Sheet 2 Range 1}, NUID@row, {Sheet 2 Range 2}, {Sheet 2 Range 2}, "ATTENDED", {Sheet 2 Range 3}, {Sheet 2 Range 3}, >=DATE(2022, 9, 14))

Which returns an "Incorrect Argument Set" result.

Any help would be much appreciated!

Devin Lee

Hey @ChloeSmith,

Try the formula below,

=INDEX(COLLECT({Sheet 2 Range 3}, {Sheet 2 Range 1}, NUID@row,{Sheet 2 Range 2}, "ATTENDED", {Sheet 2 Range 3}, @cell >= DATE(2022, 1, 14)), 1)

The COLLECT function is a range so you would place it in the range portion of the INDEX. There seems to be duplicate references to the same range in your formula which you only need one. When you reference Range 3 you need to include @cell because you are using a formula in the criteria. Lastly, you choose 1 as the row index because you want the first result from the COLLECT formula range.

=INDEX(COLLECT(C:C, A:A, NUID@row, B:B, "ATTENDED", C:C, @cell >= DATE(2022, 1, 14)), 1)

Devin Lee

Hey @ChloeSmith,

Try the formula below,

=INDEX(COLLECT({Sheet 2 Range 3}, {Sheet 2 Range 1}, NUID@row,{Sheet 2 Range 2}, "ATTENDED", {Sheet 2 Range 3}, @cell >= DATE(2022, 1, 14)), 1)

The COLLECT function is a range so you would place it in the range portion of the INDEX. There seems to be duplicate references to the same range in your formula which you only need one. When you reference Range 3 you need to include @cell because you are using a formula in the criteria. Lastly, you choose 1 as the row index because you want the first result from the COLLECT formula range.

=INDEX(COLLECT(C:C, A:A, NUID@row, B:B, "ATTENDED", C:C, @cell >= DATE(2022, 1, 14)), 1)

ChloeSmith

@Devin Lee Thank you so much!! I've been trying to fix that for days!

SCavaiani

I was going to ask a related question, but figured out the solution as I was typing this out. Still posting in case this helps anyone else.

-------

Looking for a solution to a similar issue and came across your answer. I have a similar formula with fewer criteria that I'm looking to streamline.

=INDEX(COLLECT({NCHF Donors}, {First Donation}, =1), 1)

This formula is working, but I am wondering if there is a way to change that last value (the row identifier) into a variable so I can drag the formula across a range of cells and the row id will auto populate, as if it becomes a variable instead of a value. I'd like the row ID value to match the row number within the smartsheet. Any insight?

------

Solution was to create an autonumber column assigning each unique row (which is autopopulated) an ID, then calling on that ID in place of the row value.

=INDEX(COLLECT({NCHF Donors}, {First Donation}, =1), [Donor Number]@row)

In this way, I can generate an autopopulated list of all unique donors and eliminate manual entry.