Search code examples
excelrankranking-functions

Excel Rank based on criteria

I am trying to rank the 'Max Reserve' based on 'Notification Month'. There are a number of I have tried various methods I've found online and none seem to work as they should. I've added some example data in an image.

Example data

The second image is the ranking which I'd like the formula to do, based on the value and month:

Data with Rank

Any help is greatly appreciated!

Thanks

Solution

  • Dependent on what rank you want to give if 2 or more values are identical, this might be one of possible solutions:

    =MAP(A2:A16,B2:B16,LAMBDA(a,b,MATCH(a,SORT(FILTER(A2:A16,MONTH(B2:B16)=MONTH(b)),,-1),0)))

    Result:

    enter image description here