New w/ Excel, Trying to get Max Month that meets 2 Criteria

    This should be an easy one.


    I'll attach example spreadsheet.


    I want to return the maximum month that meets two criteria, e.g., Name=David and Year=2002.


    See cell F2 in attachment.

    Your dmax approach will work, but only if you change the date entries from text (Jan, Feb) etc to date values (01/01/03 etc). I suggest you change the way you record the dates - is this possible?

    this would be possible if this were a new spreadsheet i was creating


    unfortunately this spreadsheet has been used for a couple of years and they only record the month in one column and year in another column.


    is there a way i can use the highest row number, because the way the spreadsheet gets updated the highest month will always be the highest row number that meets that year.

    OK...


    1) Insert an extra column 'Month Num' in column D, populated with:


    =month(b2&0)


    ...a 'trick' that gets excel to return the month number for text entered month names.


    2) Searching for the max of this column can be used to determine the month name you're looking for using the following:


    =INDEX(B2:B4,MATCH(MAX(IF(A2:A4=G2,IF(C2:C4=H2,D2:D4))),D2:D4,0),1)


    which needs to be array entered using control + shift + enter, not just enter.

    thanks so much


    i'll give it a try tonight or in the morning


    i really don't want to create a new column because of the way the data currently gets imported into the spreadsheet, but this definitely gives me something to think about.


    thanks again

    the extra column was merely for clarity. The same result can be returned with a single formula:


    =INDEX(B2:B4,MATCH(MAX(IF(A2:A4=G2,IF(C2:C4=H2,D2:D4))),MONTH(B2:B4&0),0),1)


    ...again, array entered.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login