Return Adjacent Column Value of a Filtered Item

Omar Cachola

Hello experts,

I am trying to figure out how to display the Status of the Primary Column after the largest Data Point has been determined.

The formula I am using under "Largest Data Point" is:

=MAX(COLLECT([Data Point]2:[Data Point]7, [Primary Column]2:[Primary Column]7, [Primary Column]@row))

So this tells me the largest numbered data point for the given Primary. Looking at the Raw Data, A has multiple entries and the largest Data Point of the 2 entries is 3.

What is the easiest way to show the Status of that row where it has A/3/Y?

I've tried to use VLOOKUP with the MAX(COLLECT()) as the search_value, but the problem I ran into was when there was multiple Data Points (take A/3/Y and D/3/X for example), it would only return the 1st row that came up having the 3 since it was the search criteria. In the example posed above, you would result with A/3/Y but also D/3/Y instead of D/3/X if that makes sense.

Please help. Thanks!

Paul Newcome

Try this...

=INDEX(COLLECT(CHILDREN(Status$1), CHILDREN([Primary Column]$1), @cell = [Primary Column]@row, CHILDREN([Data Point]$1), @cell = [Data Point]@row), 1)

Paul Newcome

The curly brackets are for cross sheet references. Square brackets are used when referencing a column in the same sheet as the formula that has numbers/spaces/special characters in it.

=INDEX(COLLECT({Status}, {Primary Column}, @cell = [Primary Column]@row, {Data Point}, @cell = [Data Point]@row), 1)

Paul Newcome

Try this...

=INDEX(COLLECT(CHILDREN(Status$1), CHILDREN([Primary Column]$1), @cell = [Primary Column]@row, CHILDREN([Data Point]$1), @cell = [Data Point]@row), 1)

Omar Cachola

Paul, that works just fine!!! However, in my working sheet, I don't have children rows since I have 2 separate sheets for raw data and the summary data. How would the formula work w/out having child rows?

Much appreciated!

Paul Newcome

You would replace CHILDREN([Column Name]1) with the range you want to evaluate [Column Name]2:[Column Name]7.

Omar Cachola

So I split the raw data and the summary data into two sheets. Then I replaced the ranges as you instructed, but I ended up getting #UNPARSEABLE. Did I do something wrong? Here is the formula that I ended up using:

=INDEX(COLLECT({Status}, {Primary Column}, @cell = {Primary Column}@row, {Data Point}, @cell = {Data Point}@row), 1)

Paul Newcome

The curly brackets are for cross sheet references. Square brackets are used when referencing a column in the same sheet as the formula that has numbers/spaces/special characters in it.

=INDEX(COLLECT({Status}, {Primary Column}, @cell = [Primary Column]@row, {Data Point}, @cell = [Data Point]@row), 1)

Omar Cachola

Thanks @Paul Newcome ! I was able to finally figure out the relationships and got it to work perfectly!!! Many thanks!

Paul Newcome

Happy to help. 👍️