Return Adjacent Column Value of a Filtered Item
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!
Best Answers

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

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)
Answers

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, 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!

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

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)

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)

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

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!