Find the maximum value in a column based on results in a different column

[Henry Costello]

Hi all,

I was hoping for some help with a function I've been trying to figure out. I'm trying to find the highest value in column A out of all of the rows that correspond to a specific value in column B.

Here's what I've come up with so far, entered as an array formula: {=LARGE(ADDRESS(IF(B1:B5="Value",ROW(B1:B5)),1),1)} but this is returning an error.

I'm not very experienced with array formulas so I'm sure I'm making a simple mistake, any help appreciated!

Cheers,

Henry

[#2 · Accepted solution]

Hello Henry

Use MAXIFS().

[#3]

Oh man, it's so simple! Thanks so much, I was obviously going down a very elaborate path!

[#4]

LARGE() had to be used until Excel 2007.

As of Excel 2010 we got AGGREGATE().

As of Excel 2016 we got MAXIFS().

And there is always Pivot and Power Query.