Forum Discussion

RShaw1972's avatar
RShaw1972
Copper Contributor
Feb 17, 2023

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 that I'm trying to construct:

Column A: listing of several employees

Column B: drop-down to select location

Column C: identify the productivity ranking of the work location selected for each employee

In other words, I manually list employees in Column A.  Column B allows me to select a work location via a drop-down list.  When I pick the work location for the employee in Column B, I need Column C to identify the productivity rank of the selected location for the employee in Column A.

 

I'm using Excel 365.

 

I hope that's clear and that someone can help.  Thanks!!

  • 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

      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!

Resources