Find an Employee's Rank by Location in Excel 365
Question details
Return an employee's productivity ranking for a selected employee and work location.

- Product
- Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- A summary sheet contains an employee and location, and the user wants to retrieve the corresponding rank from a data sheet.
- Observed behavior
- The desired result is the existing rank associated with both the selected employee and location.
On a sheet named Data Sheet, place employees in column A, locations in column B, and existing ranks in column C. On the summary sheet, enter the employee in A2 and the location in B2.
Look up the employee's rank with INDEX and MATCH
Use this formula when the source data already contains a rank for each employee and location.
The formula finds the first row where both the employee and location match, then returns the rank from column C. It retrieves an existing rank rather than calculating one from productivity scores.
On the summary sheet, select C2 and enter =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)),""). Press Enter.
Select C2 and drag its fill handle down column C to retrieve ranks for additional employee and location pairs.
If C2 is blank, compare the employee and location in A2 and B2 with the entries on Data Sheet. Adjust the formula's source ranges if your data extends beyond row 1000.

Look up employee rankings in WPS Spreadsheet
Use the same INDEX and MATCH formula in WPS Spreadsheet to retrieve an existing rank for an employee and location.
- 1. Open the workbook: Open WPS Spreadsheet, select Open, and choose the workbook containing your employee, location, and rank data.
- 2. Set up the lookup cells: On the summary sheet, enter an employee in A2 and a location in B2. Confirm that Data Sheet has employees in column A, locations in column B, and ranks in column C.
- 3. Enter the formula: Select C2 and enter =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)),""). Press Enter.
- 4. Apply it to other rows: Select C2 and drag the fill handle down to look up the remaining employee and location pairs.

Frequently Asked Questions
Does this formula calculate productivity rankings?
No. It retrieves a rank already stored in column C of Data Sheet. Calculate the ranks from productivity scores separately before using this lookup.
Why does the formula return a blank?
IFERROR displays a blank if no matching employee and location are found or another error occurs. Check A2 and B2 against Data Sheet, including extra spaces and the source range.
What happens if an employee has multiple records at the same location?
MATCH returns the first matching row, so the formula returns that row's rank. Add another lookup criterion if you need to distinguish records by date or another field.
How do I include rows beyond row 1000?
Edit the formula in C2 and extend all three source ranges to the same ending row, such as A2:A5000, B2:B5000, and C2:C5000. Then fill the revised formula down.




