Excel: Get max value from variable range

I need to retrieve the max value from a variable number of rows and columns where the row header equals a certain value, 300 in the above example would return a max of 15.

I'm trying to create a formula where the row header (e.g. 300) would be a variable so the max could be returned for row headers 300 or 600 and so on.

How do get a range or array of all the rows with 300 as the header so I can use the Max function on that range?

Put your 300 in F1, then in D1 put the array formula:

``````=MAX(IF(A1:A100=F1,B1:C100))
``````

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

Because of the column A criteria, the 101 on row 14 is ignored.

If you have Office 365 Excel, the normal formula:

``````=MAX(MAXIFS(B:B,A:A,300),MAXIFS(C:C,A:A,300))
``````

You can replace the both 300 with a cell address. Since this is not an array formula it will allow full column references without detriment.

• +1, I should have mentioned it's Office 2010 at my workplace but this is handy for future reference – Absinthe Aug 30 '17 at 13:09

Here's a somewhat more general answer:

Suppose you want to compute the MAXes (or MINs, or MEDIANs, etc.) of numeric values appearing in cols C & D for the contiguous subset of rows i to j in an array extending from rows 6 to N, with the value of "row" entered in col B. Let the explicit value of i be specified in B2 and that of j in B3 (--those values perhaps having been computed elsewhere in your spreadsheet, a priori); here, for sake of transparency, let i = 9 and j = 14, with n = 15.

Here's the Excel2003 code I've developed, which seems to work just fine:

{=MAX(IF(B6:B15>=B2,IF(B6:B15<=B3,C6:C15),""))} , which returns MAX(C9:C14) ; and,

{=MAX(IF(B6:B15>=B2,IF(B6:B15<=B3,D6:D15),""))} , which returns MAX(D9:D14) .

Just change MAX to MIN or other function name, to do analogous computation. But, don't forget to re-install (Ctrl-Shift-Enter) those "curly brackets," which will go away everytime you manipulate the statement.