Forum Discussion

RShaw1972's avatar
RShaw1972
Copper Contributor
Feb 17, 2023
Solved

Sorting and Ranking by Formula / Function?

Hello.  Here is a summary of my data table to start off: Column A: Employee Name Column B: Location Column C: (e.g. productivity rating)   Now, this is the summary table in a separate worksheet ...
excel
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Feb 17, 2023

RShaw1972 

Let's say the data are on a sheet named Data Sheet, in A2:A1000.

 

In C2 on the summary sheet:

 

=IFERROR(INDEX('Data Sheet'!$C$2:$C$1000, MATCH(1, ('Data Sheet'!$A$2:$A$1000=A2)*('Data Sheet'!$B$2:$B$1000=B2), 0)), "")

 

Fill down.

  • RShaw1972's avatar
    RShaw1972
    Copper Contributor
    Feb 17, 2023

    HansVogelaar 

    Thanks for the response.  Your formula locates the matching value in Column C for the combination of the values in Columns A and B, but it does not rank the value in Column B for the employee in Column A.  For example, "Sam" works in 5 locations with the following productivity ratings at each location:

    • Location A - 5.5
    • Location B - 7.0
    • Location C - 4.5
    • Location D - 6.0
    • Location E - 9.5

    If in Column B I select "Location D" from the drop-down list, I would like to have Column C (Productivity Rank) calculate "3", identifying "Location D" as ranking 3rd in productivity for Sam.  Is this achievable? Thanks for your help!

      • RShaw1972's avatar
        RShaw1972
        Copper Contributor
        Feb 17, 2023
        That is absolutely phenomenal. Thank-you! I'm going to take the time to learn this function.

Resources