need to find MAX value IF meets criteria

hello i need to find the maximum value for each letter from an array which will be mixed up like below. the array and number of unique letters will be alot more!

a 1 = should =7
b 2 = should = 8
c 3 = should = 9
a 4 = should =7
b 5 = should = 8
c 6 = should = 9
a 7 = should =7
b 8 = should = 8
c 9 = should = 9

Try this...


Data Range

	A	B	C
1	----	----	----
2	a	1	7
3	b	2	8
4	c	3	9
5	a	4	7
6	b	5	8
7	c	6	9
8	a	7	7
9	b	8	8
10	c	9	9

This array formula** entered in C2 and copied down:

=MAX(IF(A$2:A$10=A2,B$2:B$10))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Originally Posted by rossg

this works but i cant go down every row and press ctrl + shift + enter i have 12,000 rows..

You only need to enter the formula once then you copy it down as far as needed.