Collect formula to return a value @row in cross sheet

Devon Eddy

I'm trying to get the following formula to work: =COLLECT({4.01 Backlinking Range 6}, {4.01 Backlinking Range 1}, [Root Domain]@row, {4.01 Backlinking Range 3}, [Date Last Backlink Live]@row)

Range 6 is a range where when the other criteria are met, I want the value @row to return.

Range 1 is a a bunch of "root domains" (there will be multiples of the same ones)

Range 3 is a range of dates each task was complete.

[Date Last Backlink Live] is another collect formula that returns the max date based on the root domain: =MAX(COLLECT({4.01 Backlinking Range 3}, {4.01 Backlinking Range 1}, [Root Domain]@row, {4.01 Backlinking Range 2}, "Live")). *this formula is working perfectly*

I can use the Max formula to return the max date, but what I'm trying to do in the formula in question, is return a URL that meets that max date and has the same root domain across both sheets.

Hopefully this all makes sense.. Let me know if you need any clarification. Thank you for any help.

Leibel S

@Devon Eddy

If I understand you correctly, your collect formula should only be returning one value.

If that is the case then just wrap it inside an INDEX function.

See below bolded letter is what I added to your formula. This will return the first value from your range that the Collect function builds.

=INDEX(COLLECT({4.01 Backlinking Range 6}, {4.01 Backlinking Range 1}, [Root Domain]@row, {4.01 Backlinking Range 3}, [Date Last Backlink Live]@row),1)