Hello and thank you for your time and help,

I am trying to create a function which would extract a subset from a given range depending on the values contained in another range. The final aim is to be able to use the Percentrank function on dynamically changeable groups


A B C D E
1 Country GDP Group PercentRank
2 Albania 5000 2 ?
3 France 28000 1
4 Romania 6000 2
5 US 34000 1


The idea would to have something like Percentrank(Subset(B2:B4, C2:C4, 1), B2) with the Subset( TotalRange, GroupRange, GroupNumber) function returning the subset as a range.

From there, all I would need to do is to change a country's group to update all the ranks.

I have started with something like this but it doesn't seem to work...

Function Subset(x As Range, y As Range, V As Integer) As Range

Dim z As Range
Dim c As Cell
Dim n As Integer

    For Each c In x.Cells
Set n = c.Row
If y(1, n).Value = Valeur Then
If Not z Is Nothing Then
Set z = Union(z, c)
Else
Set z = c
End If
End If
    Next

Subs = z

End Function
Any thoughts?

Thank you in advance