Forum Discussion

PatsOnFire's avatar
PatsOnFire
Copper Contributor
Nov 03, 2021
Solved

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

  • mtarler's avatar
    mtarler
    Silver Contributor
    starting in Excel 2019 there is MAXIFS() function that can do that. What version of Excel do you have?
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor

        PatsOnFire 

        Just in case, couple of more options

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

        and

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

Resources