QUESTION: I'm not sure what I"m doing wrong here, any advice is appreciated.
hit ctrl, shift enter and it didn't calculate the max given that specification. Am I doing something incorrectly?
If you want the max value in column D considering only values that are less than the value in AY37 then it would be
=MAX(IF(($D:$D<$AY$37),$D:$D)) Entered with Ctrl+Shift+enter
Look at the formula closely because I made some major changes to it.
I assume you are using excel 2007 or later because earlier versions do not support using and entire column in an array formula.
---------- FOLLOW-UP ----------
QUESTION: Sorry Tom, I looked at it again and it's not quite returning what I'd hoped. Each row is one unique name. For all rows in which Column D is less than AY7, I'd like to return the max value in column AL.
I tried changing this to =MAX(IF(($D:$D<$AY$37),$AL:$AL)) Entered with Ctrl+Shift+enter, but that just returned the absolute max for all of column AL.
What you did is correct if AL contains the numbers.
The comparison is the only point of concern.
You said each row is a unique name, but what is being compared in Column D and AY37 (the formula shows AY37 as per your first question, but here you say AY7 (could be a typo).
Anyway, if column D and is a number and AY37 contains a number, for that row, if the number is less than the number in AY37, then the number in column AL will be returned and if not, then FALSE will be returned. The MAX function will ignore FALSE.
Now if you have blanks in column D, and it is a numerical comparison, those could be seen as less than the number in AY37. This will correct for that
but the formula should work (and that was tested as one without the extra condition and it worked for me.
If you want to send me the workbook and show where you think you are getting the wrong answer, I will take a look.