Function Problems

How to Return a Manager by Location Using Excel Formulas

Olivia Miller Sep 28, 2026 868 views

Question details

The user needs an Excel formula to look up and return a manager's name from another worksheet based on two specific criteria: the location number and the job title.

How to Return a Manager by Location Using Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Searching for specific personnel data across multiple worksheets using multiple intersecting conditions.
Observed behavior: The user wants to automatically extract the manager's name corresponding to a specific location without manually searching or filtering through the dataset.

Before you start

Ensure that both your source data sheet and your lookup sheet have consistently formatted location numbers and job titles (without trailing spaces) to avoid matching errors.

Solution 1Recommended

Use INDEX and MATCH for Multiple Criteria Lookup

Combine INDEX and MATCH in an array formula to evaluate multiple conditions simultaneously and return the correct value.

Standard lookup functions typically handle only one condition. By multiplying two arrays inside the MATCH function, we can create a boolean logic test that demands both conditions (Location and Job Title) be met to return a match.

Select the target cell

Click on the cell in your workbook where you want the manager's name to appear.

Enter the INDEX and MATCH array formula

Type the formula: =IFERROR(INDEX(Sheet2!A:A,MATCH(1,(Sheet2!C:C=A2)*(Sheet2!B:B="manager"),0)),"Not Found")

Adjust cell references

Change 'Sheet2!A:A' to the column containing the names you want to return. Adjust 'Sheet2!C:C' and 'A2' to represent the location column in your data and the location cell you are searching for.

Apply the array formula

If you are using an older version of Excel, press Ctrl+Shift+Enter to evaluate the formula as an array. Newer versions (Microsoft 365) allow you to just press Enter.

Use INDEX and MATCH for Multiple Criteria Lookup

Array Formula Requirement: Failing to use Ctrl+Shift+Enter in legacy Excel versions will result in a #VALUE! error because standard evaluation cannot process the array multiplication.

Solution 2

Use XLOOKUP for Multiple Criteria (Newer Versions)

If you have a modern version of Excel, XLOOKUP provides a simpler, more intuitive syntax for multiple criteria lookups.

WPS Spreadsheet Formula Engine

Effortlessly Manage Complex Lookups with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, including INDEX, MATCH, and XLOOKUP, allowing you to handle multiple-criteria lookups seamlessly across large datasets.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your location and manager data.
2. Select the destination cell: Click the cell where you want the manager's name to be returned.
3. Input the array formula: Paste your multiple-criteria INDEX/MATCH or XLOOKUP formula into the formula bar.
4. Evaluate the formula: Press Enter (or Ctrl+Shift+Enter) to instantly return the matched manager's name without compatibility issues.

100% compatible with Microsoft Excel formulas and functionsSupports modern functions like XLOOKUP for easier multi-criteria searchesRobust array formula engine that handles large datasets quicklyFree, lightweight, and fully compatible with .xlsx formats

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDEX MATCH formula return a #VALUE! error?

A #VALUE! error typically occurs if you did not press Ctrl+Shift+Enter in older versions of Excel when using array formulas. It can also happen if the ranges you are comparing are not the exact same size (e.g., comparing C1:C100 with B1:B90).

Can I use VLOOKUP to find a manager by location and job title?

Standard VLOOKUP only handles a single lookup criterion. To use VLOOKUP for multiple criteria, you must create a "helper column" in your source data that concatenates the location and job title, and then search against that combined string. Using INDEX and MATCH bypasses the need for helper columns.

Is the formula case-sensitive when looking for the word 'manager'?

Standard INDEX, MATCH, and XLOOKUP functions are not case-sensitive, meaning 'Manager', 'manager', and 'MANAGER' are treated identically. If you require case-sensitive matching, you must incorporate the EXACT function into your array formula.

How do I look up data across entirely different workbooks?

You can reference different workbooks in your formula by including the workbook name in square brackets, such as '[Data.xlsx]Sheet2!A:A'. Be aware that both workbooks generally need to be open for dynamic array formulas to update automatically without resulting in reference errors.

Excel & Data Problems