Finding largest number from list

[holyman]

Hoping someone can help....as couldn't find anything relevant in the forum.

In sheet 1, I have coulmns of data that includes below. In sheet 2 i need
to return the highest value that is in column B if column A is 'Kerry
Beniston1'. In this instance it would be 22Kerry Beniston.
If it could just return the value of 22, that would be even better, but if
necessary, can live with the whole text.

Please can anyone help. Many thanks


Column A Column B
KERRY BENISTON0 0KERRY BENISTON
KERRY BENISTON0 0KERRY BENISTON
KERRY BENISTON0 0KERRY BENISTON
KERRY BENISTON1 16KERRY BENISTON
KERRY BENISTON1 17KERRY BENISTON
KERRY BENISTON1 22KERRY BENISTON
KERRY BENISTONFALSE 1KERRY BENISTON

[Franz Verga]

holyman wrote:
> Hoping someone can help....as couldn't find anything relevant in the
> forum.
>
> In sheet 1, I have coulmns of data that includes below. In sheet 2 i
> need to return the highest value that is in column B if column A is
> 'Kerry Beniston1'. In this instance it would be 22Kerry Beniston.
> If it could just return the value of 22, that would be even better,
> but if necessary, can live with the whole text.
>
> Please can anyone help. Many thanks
>
>
> Column A Column B
> KERRY BENISTON0 0KERRY BENISTON
> KERRY BENISTON0 0KERRY BENISTON
> KERRY BENISTON0 0KERRY BENISTON
> KERRY BENISTON1 16KERRY BENISTON
> KERRY BENISTON1 17KERRY BENISTON
> KERRY BENISTON1 22KERRY BENISTON
> KERRY BENISTONFALSE 1KERRY BENISTON


Hi Holyman,

try this array entered formula (i.e. you should press Ctrl+Shift+Enter
instead of just Enter):

=MAX(IF(A34:A40="KERRY BENISTON1",VALUE(LEFT(B34:B40,LEN(B34:B40)-LEN("KERRY
BENISTON"))),""))&"KERRY BENISTON"

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy