Help with Finding Highest value

[spgprivate]

Can anyone advise best way to search through a number of rows, some wit
the same heading to find the row with the highest value for example

1 Apples 10
2 Apples 22
3 Orange 19
4 Pears 23
5 Mangoe 33
6 Apples 90

I need a formula that return the value '90' when searched agains
Apples.

Hope that makes sense.

Simo

 

[SteveG]

Assuming your data is in A1:B6,

=MAX(IF(A1:A6="Apples",B1:B6,0))

HTH

Stev

 

[Bearacade]

Going 1 step further with SteveG's example:

You can put this in a cell (other than A1:B6 or C1) and just type th
value you want to search for in C1

=MAX(IF(A1:A6=C1,B1:B6,0)

 

[SteveG]

Simon,

If your data is combined in one cell like A1 = "Apples 10" and you
criteria Apples is in C1,


=MAX(IF(LEFT(A1:A6,FIND(
",A1:A6,1)-1)=C1,VALUE(RIGHT(A1:A6,LEN(A1:A6)-FIND(" ",A1:A6,1))),0))


Which is an array formula so commit with Ctrl-Shift-Enter not jus
enter.

HTH

Stev