Matching highest and lowest values by category

**konradz** · 08-28-2012, 08:02 AM

I hope you don't mind that I'm going to make use of the opportunity and refresh this discussion. I'm looking to write a formula that would match a given percentage of highest and lowest values from a data table according to the custom threshold and category. The data table looks like that:

Identifier	Category	Value
ABC1	Category A	123
ABD2	Category A	5454564654
XYZ56	Category A	56454564
YUC67	Category A	656454566
FGH78	Category A	45454564564
FGT55	Category A	656545454
MNO90	Category A	212313213
ABDD32	Category B	545646
KOLP12	Category B	454545
KOSA134	Category B	454545
HUI89	Category B	454545
KOLP12	Category B	454545
KOLP14	Category B	45454544

I'm aiming to get the highest and lowest values from the Category A and Category B separately and match them with corresponding identifiers. It is possible that there will be few the same values within each category, I would have to get all of them. All the formulas (or pivot tables) will have to be working under the Excel 2003. I'll appreciate any help

**canapone** · 08-28-2012, 08:40 AM

Hi,

please see the little example attached

=INDEX($A$2:$A$1000,MATCH(MIN(IF($B$2:$B$1000=B2,$C$2:$C$1000))&B2,$C$2:$C$1000&$B$2:$B$1000,0))

=INDEX($A$2:$A$1000,MATCH(MAX(IF($B$2:$B$1000=B2,$C$2:$C$1000))&B2,$C$2:$C$1000&$B$2:$B$1000,0))

All the formulas must be confirmed with control+shift+enter.

Cheers