SOLVED

Sorting and Ranking by Formula / Function?

Copper Contributor

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!!

4 Replies

@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.

@Hans Vogelaar 

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!

best response confirmed by RShaw1972 (Copper Contributor)
Solution

@RShaw1972 

See the attached sample workbook.

That is absolutely phenomenal. Thank-you! I'm going to take the time to learn this function.
1 best response

Accepted Solutions
best response confirmed by RShaw1972 (Copper Contributor)
Solution

@RShaw1972 

See the attached sample workbook.

View solution in original post