Sorting and Ranking by Formula / Function?

New 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


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 (New Contributor)


See the attached sample workbook.

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