MATCH not Returning correct value

[szymagic]

Hello all, this is driving me absolutely mad, MATCH is returning the wrong value, for only this set of data.

[TABLE="width: 320"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]0.59
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]30
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]0.37
[/TD]
[TD="bgcolor: transparent, align: right"]35
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1.31
[/TD]
[TD="bgcolor: transparent, align: right"]55
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]30
[/TD]
[TD="bgcolor: transparent, align: right"]0.59
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]35
[/TD]
[TD="bgcolor: transparent, align: right"]0.37
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]40
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]45
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]50
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]55
[/TD]
[TD="bgcolor: transparent, align: right"]1.31
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]60
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]65
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]70
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[/TR]
</tbody>[/TABLE]
Where you see "10" at the bottom is the formula =MATCH(MAX(F5:F14),F5:F14) -------------[F5:F14 is the range including 0.59, 0.37 and 1.31]


The formulas in the F5:F14 are {=SUM(IF(($C$2:$C$4=E6),$B$2:$B$4,0))} ---------- E6 in this case is referencing "25" and that formula is dragged down through "70"


Clearly the max value is in position 7 not 10.


Thanks for any feedback!

 

[63falcondude]

Add a zero at the end of your match formula to specify the match type.

Formula would become =MATCH(MAX(F5:F14),F5:F14,0)

 

[szymagic]

Wow, am I foolish!?

Thanks, works perfect, I knew it had to be something simple I was overlooking!

 

[63falcondude]

Wow, am I foolish!?

Thanks, works perfect, I knew it had to be something simple I was overlooking!

Glad to help! Although it's optional, I always include the match type to avoid situations like this. For some reason, it chose to use 1 (Less than) instead of 0 (Exact match).