Returning max number of a certain item

[campelliann]

Hi,

I have a problem that is analogous to this:

a 4

b 5

a 2

b 7

c 8 etc.

I want to build a formula that gives me the maximum a,b,c. For building a new table with these items and its maximum values:

a 4
b 7
c 8

Thanks

[BlindAlley]

You can use the =MAX(IF( array formula to accomplish that.

See sample attached

[Czeslaw]

Please Login or Register  to view this content.

[AlKey]

Try this

=MAX(INDEX(($A$1:$A$9=D1)*($B$1:$B$9),0))

Row\Col	A	B	C	D	E
1	a	15		a	65
2	a	4		b	45
3	b	45		c	57
4	b	32
5	b	45
6	c	57
7	c	56
8	b	12
9	a	65

[BlindAlley]

I like that Mr AltKey

[campelliann]

You guys are great! thank you very much Blindalley, Alkey and Czeslaw!

[AlKey]

You're welcome. Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E
1	------	------	------	------	------
2	A	4		A	4
3	B	5		B	7
4	A	2		C	8
5	B	7
6	C	8
7

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

=MAX(IF(A$2:A$6=D2,B$2:B$6))

** 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.

[Czeslaw]

Your problem can be solved at all without any formulas, but only with the help of consolidation.