IF and MAX combined formula

[Guest]

Based on the data below, I am trying to return in column C the highest value.

i.e If asked to return the highest value for all those in column with text
of 'BRITISH GASCOMM1' the answer would be 122.
The next row could be referencing AFFLIATESCAR1 which would return 134.

With not knowing how many rows of data and the order of the data, am needing
help to find a formula. Please help. Example data below

Column A Column B
AFFILIATESCAR1 106
AFFILIATESCAR1 134
AGENCIESCAR0 70
AGENCIESCAR0 81
BRITISH GASCAR1 183
BRITISH GASCAR0 75
BRITISH GASCOMM1 106
BRITISH GASCOMM1 106
BRITISH GASCOMM1 107
BRITISH GASCOMM1 107
BRITISH GASCOMM1 107
BRITISH GASCOMM1 122
BRITISH GASCOMM0 70
BRITISH GASCOMM0 70
BRITISH GASCOMM0 70
BRITISH GASCOMM0 70
BRITISH GASCOMM0 70
BRITISH GASCOMM0 70
BRITISH GASCOMM0 70
BRITISH GASCOMM0 70
BRITISH GASCOMM0 70
BRITISH GASCOMM0 71

 

[Don Guillett]

this is an array formula which must be entered/edited with ctrl+shift+enter
CSE
=MAX(IF((Q2:Q22="british gascomm1"),(R2:R22)))

 

[Guest]

Thanks for that.......however, I've never had to do a ctrl+shift+enter
command / array formula. Please can you expand on whats required

 

[Guest]

Thanks for that.........however I've never had to use the ctrl+shift+enter
array formula beofre.............please can advise more in further detail.
thanks

 

[Don Guillett]

instead of enter when you create/edit the formula hold down the control key
AND the shift key and touch the enter key.

 

[Guest]

Would it be different, if I am needing the results in a seperate sheet.
I enter the formula and get 918 as a result instead of 122

 

[Guest]

thanks muchly, now working, used the wrong keys to enter