Search code examples
excelranking

Using RANK, OFFSET and MATCH to find rank after an adjustment

I have a formula that alters the margin on a premium in 'Sheet1' (column name: Adj_premium), when the premium is adjusted its relative place in the rankings of other premiums in the 'rank_lookup' table changes accordingly. Each row in the premium data in 'Sheet1' has a unique reference number (column name: UniqREF) that corresponds to a column header in the 'rank_lookup' table. What I am trying to do is return the relative rank for each row in 'Sheet1' from the values stored in the 'rank_lookup' sheet. I tried the following, but it returns an N/A;

=RANK([@[Adj_premium]],OFFSET(rank_lookup!$B:$B,0,MATCH([@UniqREF],rank_lookup!$B$5:$JB$5,0),1,1),0)

Thanks in advance

Ben

'Sheet1'

'rank_lookup' table

Solution

  • Assuming your data in the RANK table is in descending order, this should work:

    =MATCH(C7,OFFSET(rank_lookup!$A$3:$A$73,0,MATCH(D7,rank_lookup!$B$2:$E$2,0)),1)