enter image description here

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:


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.

enter image description here

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


If you have Office 365 Excel, the normal formula:


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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.