# Complex Formula to return latest date a specific value was used and return a value from a dif column

Alright, I've got a pretty complex formula (at least for me..) that I've been banging my head against and could definitely use a little help from the awesome community!

Here's what I am trying to do (see screenshots for reference).

I'm attempting to create a formula in the "Latest Backlink Live" Column. I would like the formula to look at the "root domain@row" in screenshot 1 and then use that value, to search "Backlink" column in Screenshot 2. to return the array of rows that contain that root domain from screenshot 1.

The next step with this array is to segment the exact row I'm looking for. I'd only like it to count the row if column status = live & then bring back the one that has the latest date.

Thank you for any help solving this one! -Devon

Tags:

• ✭✭✭✭✭✭

You had an extra paren. Try this:

To get the date: =MAX(COLLECT({4.01 Backlinking Range 3}, {4.01 Backlinking Range 1}, [root domain]@row, {4.01 Backlinking Range 4}, "Live"))

It looks like your back link doesn't exactly match the root domain. If so, you should add a CONTAINS function: =MAX(COLLECT({4.01 Backlinking Range 3}, {4.01 Backlinking Range 1}, CONTAINS([root domain]@row), {4.01 Backlinking Range 4}, "Live"))

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭
edited 12/20/20

Hi @Devon Eddy ,

This formula might help you move in the right direction. It should return the newest date of the live backlink equal to the root domain. Since you are working with 2 sheets the cell ranges will need to be external sheet references; you can't copy and paste it:

What did you want to return besides the date?

Hope this is some help.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• Hey @Mark Cronk thanks for the response!

I tried inputting your formula and replacing everything with cross sheet formulas and am not able to get it to function properly.

Here's how I have it setup: =MAX(COLLECT({4.01 Backlinking Range 3}, {4.01 Backlinking Range 1}), [root domain]@row), {4.01 Backlinking Range 4}, "Live")

It is returning Unparsable

Besides the date, I'm trying to return the value of the latest backlink @row that contains the root domain from the sheet in screenshot 1.

• Alright @Mark Cronk I got it to work with following formula! =MAX(COLLECT({4.01 Backlinking Range 3}, {4.01 Backlinking Range 1}, [Root Domain]@row, {4.01 Backlinking Range 2}, "Live"))

Now, the other piece I'm trying to get is the actual "backlink" value that is correlating with the max date we just accomplished in the other column. (see screenshot)

I feel like I am very close but cannot get it to work. Here's the formula I have worked out so far: =COLLECT({4.01 Backlinking Range 6}, {4.01 Backlinking Range 1}, [Date Last Backlink Live]@row, {4.01 Backlinking Range 3}, [Date Last Backlink Live]@row)

Range 6 is the Backlink Column (the value I want to actually bring in)

Range 1 is the root domain (same on both sheets)

Range 3 is the date column which should be the same as [Date Last Backlink Live]@row since we just came up with that in the previous formula.

Appreciate any further help very much!

• ✭✭✭✭✭✭

You had an extra paren. Try this:

To get the date: =MAX(COLLECT({4.01 Backlinking Range 3}, {4.01 Backlinking Range 1}, [root domain]@row, {4.01 Backlinking Range 4}, "Live"))

It looks like your back link doesn't exactly match the root domain. If so, you should add a CONTAINS function: =MAX(COLLECT({4.01 Backlinking Range 3}, {4.01 Backlinking Range 1}, CONTAINS([root domain]@row), {4.01 Backlinking Range 4}, "Live"))

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭

Glad you solved the 1st part while I was typing.