Forum Discussion
PatsOnFire
Nov 03, 2021Copper Contributor
Formula for: Filter an array based on a referenced value, then select largest value in other column
Hello,
I am looking for some sort of "LARGEIF" formula. Here is my dataset. Column D represents expected results from the formula I need.
In words, I would put the following formula in cell D2:
- For entire dataset:
- Consider only rows that have column A = cell A2
- Then, consider only rows that have column B = cell B2. Mockup result using actual filters:
- Then, using the resulting filtered dataset, get the largest value in column C. The largest value is 7:
- copy down this formula in column D to get the following results:
Can this be achieved?
Thanks
- starting in Excel 2019 there is MAXIFS() function that can do that. What version of Excel do you have?
4 Replies
- mtarlerSilver Contributorstarting in Excel 2019 there is MAXIFS() function that can do that. What version of Excel do you have?
- PatsOnFireCopper ContributorWorks! Thank you
- SergeiBaklanDiamond Contributor
Just in case, couple of more options
=AGGREGATE(14,6, range/condition1/condition2,1)and
=MAX( FILTER( range,condition1*condition2 ) )