Formula help

[Ron99]

Hello,

I have a below data in columns

[TABLE="width: 500"]
<tbody>[TR]
[TD]ron
[/TD]
[TD]4
[/TD]
[TD]shawn
[/TD]
[TD]3
[/TD]
[TD]max
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]jack
[/TD]
[TD]2
[/TD]
[TD]rex
[/TD]
[TD]1
[/TD]
[TD]swan
[/TD]
[TD]12
[/TD]
[/TR]
</tbody>[/TABLE]


The next column I need a output showing the maximum. example - in the 1st row the maximum number is 8 but I need the ouput as max, same with the 2nd row, maximum is 12 and I need the output swan.

Please let me know the formula which I can use.

Thanks,
Ron

 

[Marcelo Branco]

Try

=INDEX(A1:F1,MATCH(MAX(A1:F1),A1:F1,0)-1)

M.

 

[GiorgiPepe]

you could use this little formula:

Function FindMax(MaxVal As Integer, SearchRng As Range)


Dim cell As Range


For Each cell In SearchRng
If cell = MaxVal Then
FindMax = cell.Offset(0, -1)
Exit For
End If
Next cell


End Function

 

[Ron99]

Thank you very much...cheers!!