I am struggling to understand what this formula is doing?

Maybe practice with =maxifs()

First, you cannot use MAXIFS if you are truly using Excel 2010, as your profile states.

Second, in Excel 2010, a slightly simpler syntax would be:

{ =MAX(IF($A$1:$A$4543=A4, IF($G$1:$G$4543<G4, $G$1:$G$4543))) }

You do not type the curly braces. Instead, you must be careful to array-enter the formula by pressing ctrl+shift+Enter instead just Enter after typing the formula =MAX(...) .

(And note that the left curly brace is left of "=", not right of it as you wrote.)

To answer your question (understand the formula), the formula calculates the max of only those values in G1:G4543 in rows where the corresponding value in A1:A4543 equals A4 __and__ the value in G1:G4543 is less than G4.

Since A4 and G4 are relative references, they will change to A5 and G5, A6 and G6 etc as the formula is copied down a column.

We can __not__ use simpler syntax like =MAX(IF(AND($A$1:$A$4543=A4,$G$1:$G$4543<G4),$G$1:$G$4543)) in a normally-entered (or array-entered) formula. It will "work"; read: not produce an error. But it will not select from G1:G4543 based on a row-by-row AND of the conditions, as intended. So it usually returns the wrong value, except by coincidence.

If you can use =MAXIFS($G$1:$G$4543, $A$1:$A$4543, A4, $G$1:$G$4543, "<"&G4) , you are using a later version of Excel than Excel 2010. You should update your profile, or at least mention the Excel version in your posting.