SOLVED

New Contributor

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

4 Replies

# Re: Sorting and Ranking by Formula / Function?

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.

# Re: Sorting and Ranking by Formula / Function?

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)
Solution

# Re: Sorting and Ranking by Formula / Function?

See the attached sample workbook.

# Re: Sorting and Ranking by Formula / Function?

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