Index Collect

Aimee Armitage

I am trying to collect a date based on a certain time point from another sheet by using INDEX(COLLECT) but something is not adding up. The Reference Sheet - IE Tracking Range 2 is the date I wanted populated in the cell. The Reference Sheet - IE Tracking Range 3 is the time point I am basing it off of but when I fill out the equation below I am getting invalid value. What am I missing?

=INDEX(COLLECT({Reference Sheet- IE Tracking Range 2}, {Reference Sheet- IE Tracking Range 3}, [Patient ID Number]@row), 1)

Mark.poole

yea the criteria would be cycle 2. So would the subject id be the patient id? If so.

=INDEX(COLLECT({Reference Sheet- IE Tracking Range 2}, “Cycle 2”,{Reference Sheet- IE Tracking Range 3}, [Patient ID Number]@row), 1)

Aimee Armitage

Thanks Mark! I got it to work with something similar: =IFERROR(INDEX(COLLECT({Reference Sheet- IE Tracking Range 1}, {Reference Sheet- IE Tracking Range 3}, [Patient ID Number]@row, {Reference Sheet- IE Tracking Range 2}, "Cycle 2"), 1), "")

Mark.poole

@Aimee Armitage

You are missing a Criterion and a criteria.

You need the Criteria for the Range {Reference Sheet- IE Tracking Range 3}. and what is the range your looking at [Patient ID Number]@row) and its criteria

=Index(Collect(Look up range, Criteria range1,Criteria 1,Criteria range 2, Criteria 2, ….),1)

Aimee Armitage

I am still confused on the criteria and criterion. In my reference sheet (seen below), I want to pull over the received date for subjects on Cycle 2 into my main sheet.

In my main sheet, I am trying to enter the formula to have the date pull over for the patient for Cycle 2:

Range 1 would be 'Received Date', range 2 would be the 'Time point' and then would the criteria be 'Cycle 2'?

Mark.poole

yea the criteria would be cycle 2. So would the subject id be the patient id? If so.

=INDEX(COLLECT({Reference Sheet- IE Tracking Range 2}, “Cycle 2”,{Reference Sheet- IE Tracking Range 3}, [Patient ID Number]@row), 1)

Aimee Armitage

Thanks Mark! I got it to work with something similar: =IFERROR(INDEX(COLLECT({Reference Sheet- IE Tracking Range 1}, {Reference Sheet- IE Tracking Range 3}, [Patient ID Number]@row, {Reference Sheet- IE Tracking Range 2}, "Cycle 2"), 1), "")