Index/Match for the largest value that matches

[newguy]

I am trying to figure out how to use and Index/Match to return the
largest value that matches the criteria. My data is unsorted to the
Match types don't help me much. An overview would be that I have Table
that contains Apartment Types (ie EFF, 1, 2, 3 bedroom etc) in one
column and the associated rent in the other. So my Match is matching
the Bedroom type and the associated rent however my list contains
multiple instances of each Apartment type but I just want to return
the highest Rent associated with each Type.


Apartment Rent
1 Bedroom 450
2 Bedroom 589
1 Bedroom 650
1 Bedroom 780
1 Bedroom 450
2 Bedroom 980
1 Bedroom 350
2 Bedroom 875

So it would return

1 Bedroom 780
2 Bedroom 980

Thanks

 

[Don Guillett Excel MVP]

I am trying to figure out how to use and Index/Match to return the
largest value that matches the criteria. My data is unsorted to the
Match types don't help me much. An overview would be that I have Table
that contains Apartment Types (ie EFF, 1, 2, 3 bedroom etc) in one
column and the associated rent in the other. So my Match is matching
the Bedroom type and the associated rent however my list contains
multiple instances of each Apartment type but I just want to return
the highest Rent associated with each Type.

Apartment        Rent
1 Bedroom       450
2 Bedroom       589
1 Bedroom       650
1 Bedroom       780
1 Bedroom       450
2 Bedroom       980
1 Bedroom       350
2 Bedroom       875

So it would return

1 Bedroom     780
2 Bedroom     980

Thanks

This is an ARRAY formula so it must be entered/edited using ctrl+shift
+enter
=MAX(IF(($G$2:$G$14=G2),$H$2:$H$14))