Function Problems

Find an Employee's Rank by Location in Excel 365

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 868 views

Question details

Return an employee's productivity ranking for a selected employee and work location.

Find an Employee's Rank by Location in Excel 365
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the lookup formula

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.

2
Fill the formula down

Select C2 and drag its fill handle down column C to retrieve ranks for additional employee and location pairs.

3
Check the result

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 the employee's rank with INDEX and MATCH
Duplicate matches: If the same employee and location appear more than once, the formula returns the rank from the first matching row. IFERROR returns a blank when no match is found or another error occurs.
Work with employee rankings in WPS

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. 1. Open the workbook: Open WPS Spreadsheet, select Open, and choose the workbook containing your employee, location, and rank data.
  2. 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. 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. 4. Apply it to other rows: Select C2 and drag the fill handle down to look up the remaining employee and location pairs.
Match both employee and location to retrieve a stored rankOpen and edit Microsoft Excel XLSX workbooksFill the formula down for multiple employees
microsoft office alternative - wps office

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.