Forum Discussion

MuzzyB's avatar
MuzzyB
Copper Contributor
Feb 05, 2019
Solved

Excel - Matching different column names to Ranks

I have a dilemma. So basically I have a column for scenarios and a column for losses. To show the losses ranked from worst to best, I had to format it and change the settings, but now this leaves my ...
excel
Excel Online
Formulas and Functions
  • Nauthstar's avatar
    Nauthstar
    Feb 06, 2019

    Hi,

    You should be able to copy the following formula into cell "A2" of your 'Ranking Losses' tab...

    =INDEX(Scenario!A:A,MATCH(C2,Scenario!G:G,0),0)

     

    If you want to check it's working...  

    first just copy the above formula into cell "A2" then copy THAT CELL and paste that formula into cells "A12" and "A18", the results should continue to show '4' and '22' respectively.

    Otherwise you can just FILL the formula down.

     

    All this assumes that your column "G" in the 'Scenario' tab is only set to display 2 decimal places and not rounded to show DPs. I've included a spreadsheet for info.

     

    Hope that helps.
    Regards
    N

Nauthstar's avatar
Nauthstar
Iron Contributor
Feb 06, 2019

Hi,

You should be able to copy the following formula into cell "A2" of your 'Ranking Losses' tab...

=INDEX(Scenario!A:A,MATCH(C2,Scenario!G:G,0),0)

 

If you want to check it's working...  

first just copy the above formula into cell "A2" then copy THAT CELL and paste that formula into cells "A12" and "A18", the results should continue to show '4' and '22' respectively.

Otherwise you can just FILL the formula down.

 

All this assumes that your column "G" in the 'Scenario' tab is only set to display 2 decimal places and not rounded to show DPs. I've included a spreadsheet for info.

 

Hope that helps.
Regards
N

  • MuzzyB's avatar
    MuzzyB
    Copper Contributor
    Feb 07, 2019

    Perfect. Thankyou very much.

Resources