SOLVED

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

Copper 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 (Copper 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 ) )
1 best response

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

View solution in original post