MAX Formula Only Working for the First Cell

[ExcelNovice456]

I am a first-time poster and am having an issue getting the MAX function to compute the correct value. The formula works for the first cell, but I want to generate a series of maximum values and it is not working for the subsequent cells. I attached a sample spreadsheet. The problem area is shaded in blue in the spreadsheet.

Here is what I am trying to do: I have values in column A, with values appearing one or more times down the column. There are numerical values in column B. I want to know the highest value in column B for each unique value in Column A. The only way I know of doing this is to dedupe Column A and then send each of the unique values through a MAX function to find the maximum value that appears for each of the column A values. This is the formula I am using:

Please Login or Register  to view this content.

It works for E2, but when duplicating the function in the next row and looking for the max value for the team name in E3, a "0" is returned, whereas a "1" should be returned. This is what the formula in that cell looks like:

Please Login or Register  to view this content.

What am I doing wrong? Thanks in advance.

[CK76]

You need to array enter the formula.

Go into cell edit mode. Then confirm your formula using CTRL + SHIFT + ENTER.

However, using entire column in this formula construct isn't recommended. Use dynamic named range or structured table.

See attached.

[ExcelNovice456]

Thank you so much! I really appreciate that you provided not only the CTRL + SHIFT + ENTER tip, but also the named range. Very helpful!

[CK76]

You are welcome

If you are satisfied with the solution provided. Please mark the thread as solved, using thread tools found at top of your initial post.

[ExcelNovice456]

Thanks for that tip too! Just closed it out.