Try this instead:

=IF(K2=SUMPRODUCT(MAX((A2:A20000=A2)*(K2:K20000))) ,"Max","")

"Erika" wrote:

The Values in column A are unit numbers and the values in column K are values
that were entered they are not the result of a formula? When I double click
in each cell and go into edit mode and then press enter to exit all of the
results are changing from #value to Max. However they all say Max.

"Sean Timmons" wrote:

And you verified none of the cells in column A and column K have a #VALUE in
them... I don't see any error in the formula that would cause a #VALUE.

"Erika" wrote:

Actually I am not getting it to work I am getting a #Value error - the
forumla is erroring out in the first line
K2=MAX(($A$2:$A$20000=A2)*$K$2:$K$20000 it is trying to find the highest
value for each unit the units are in column A.

Why would I be gettingthe #value error?

"Sean Timmons" wrote:

Sounds like one of the cells in either A or K is #VALUE. works fine for me...

"Erika" wrote:

I use to have this formula working in Office 2003 and now I am getting a
value error - anyone see what is going wrong?

=IF(K2=MAX(($A$2:$A$20000=A2)*$K$2:$K$20000),"Max" ,"")

It is suppose to look through the list find the hight value and put the work
max in the cell with the highest value