Thread: index match formula?

Hi all thanks for reading, i have attached a sample of the data i am working with... Basically I've got the following

Col A-----------------------------Col B---------------------Col C--------------------Col D
Building ID---------------------Count---------------------Rental Rate-----------Rank

in col A i have a list of building id's, including duplicates as each building may have one or more rental rates and each rental rate generates a record. I have used count in col B as i thought this might help. Col C contains the rental rates for each corresponding building ID and in Col D i want to rank the rental rates for each building separately.

The closest i've come (and i've tried several different trains of thought) is
=IF(B2=1,1,IF(B2=2,RANK(C2,$C2:$C3,0),IF(B2=3,RANK(C2,($C2:$C4),0),0)))

but as you can immediately see this fails when there are 2 or more occurances of a building id as the reference needs to apply to 2 or 3 rows, depending on how many occurances of the buidling id.

For example if i have...
Col A-----------------------------Col B---------------------Col C--------------------Col D
Building ID---------------------Count---------------------Rental Rate-----------Rank

07644525----------------------1-----------------------------------10--------------------------1
07644675----------------------1-----------------------------------7--------------------------1
07755000----------------------1-----------------------------------11.5--------------------------1
08149000----------------------1-----------------------------------5--------------------------1
08457000----------------------1-----------------------------------10--------------------------1
09392000----------------------1-----------------------------------10--------------------------1
00802000----------------------2-----------------------------------10--------------------------1
00802000----------------------2-----------------------------------9.5--------------------------1
00842000----------------------2-----------------------------------7--------------------------1
00842000----------------------2-----------------------------------5--------------------------2
00843000----------------------2-----------------------------------7--------------------------1
00843000----------------------2-----------------------------------5--------------------------2
00877000----------------------2-----------------------------------10--------------------------1
00877000----------------------2-----------------------------------9.5--------------------------2
00884000----------------------2-----------------------------------10--------------------------1
00884000----------------------2-----------------------------------9.5--------------------------1
00889000----------------------2-----------------------------------8.5--------------------------1
00889000----------------------2-----------------------------------8--------------------------2
05469000----------------------3-----------------------------------6--------------------------1
05469000----------------------3-----------------------------------5--------------------------2
05469000----------------------3-----------------------------------1.4--------------------------3
11162750----------------------3-----------------------------------5.5--------------------------1
11162750----------------------3-----------------------------------3--------------------------2
11162750----------------------3-----------------------------------2.75--------------------------3
19000000----------------------3-----------------------------------10--------------------------1
19000000----------------------3-----------------------------------9.5--------------------------1
19000000----------------------3-----------------------------------5--------------------------1


You can see that the rank does not work with multiple entries because the ref array in the rank ($C2:$C3,0),IF(B2=3,RANK(C2,($C2:$C4) would need to look like $C$2:C$3 for 2 occurances and $C$2:$C$4 for 3 occurances, however formatted like this obviously would apply only this series of ref cells down the whole list...

I need it to be able to find and rank however many rental rates are associated with each bldg id... i don't know if Index and Match would do this? I think if there were such thing as a RANKPRODUCT like a sumproduct formula, that might work... any ideas?

Thanks,
Joe

Thanks for the reply! This is basically one step out of a larger sheet that i am trying to put together... the result i'm looking for is

ID----------------------Rate------------------Rank
xxxxxxx-------------$5-----------------------1
xxxxxxx-------------$4-----------------------2
xxxxxxx-------------$2-----------------------3 and then repeat for the next id....
xxxxxxy-------------$10---------------------1
xxxxxxy-------------$5-----------------------2 and then repeat.... each id may have between 1 and at most probably 5 associated rates, so i want to rank all the rates specific to each id. In some cases it may only be one rate in other cases there will be multiple.

After this is complete, i want to concatenate the Id with the rank so that i will have a unique id for each rate that i can then apply to another sheet....

Thanks,
joe

thanks for the advice, but i think another user on another forum has figured it out and it looks really simple, but i don't think i understand why it works....

E2=COUNTIF(A2:$A$7,A2) and paste down
E3=COUNTIF(A3:$A$7,A3) etc

ok, that makes some sense, but i do have another question... in the formula =CountIF(A2:$A$N,A2) where $A$N is the end of the data set... is there a wildcard or something i can replace N with so that if i use this in a macro it will automatically apply it to the whole data set?

Thanks,
Joe