Forum Discussion
RShaw1972
Feb 17, 2023Copper 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!!
See the attached sample workbook.
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.
- RShaw1972Copper Contributor
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!