SOLVED

Formula for: Filter an array based on a referenced value, then select largest value in other column

New Contributor

Hello,

I am looking for some sort of "LARGEIF" formula. Here is my dataset. Column D represents expected results from the formula I need.

PatsOnFire_2-1635975126866.png

 

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:

PatsOnFire_3-1635975384647.png

 

- Then, using the resulting filtered dataset, get the largest value in column C. The largest value is 7:

PatsOnFire_4-1635975458122.png

- copy down this formula in column D to get the following results:

PatsOnFire_5-1635975633530.png

 

 

Can this be achieved?

 Thanks


4 Replies
best response confirmed by PatsOnFire (New Contributor)
Solution
starting in Excel 2019 there is MAXIFS() function that can do that. What version of Excel do you have?

@PatsOnFire 

Just in case, couple of more options

=AGGREGATE(14,6, range/condition1/condition2,1)

and

=MAX( FILTER( range,condition1*condition2 ) )