Function Problems

How to Automatically Return a Department Based on a Manager's Name in Excel

Elise Williams Oct 10, 2026 869 views

Question details

The user needs to automatically populate an adjacent cell with a department name when a specific manager's name is entered, using a reference lookup table.

How to Automatically Return a Department Based on a Manager's Name in Excel

Product: Excel
Device & OS: not provided
Scenario: Automating data entry and organizing employee records by linking manager names to their respective departments using a lookup formula.
Observed behavior: The target cell displays the correct department from the reference table automatically upon entering a manager's name.

Before you start

Ensure your reference table is well-organized, with the manager's name in the first column of the range and the corresponding department in an adjacent column to the right.

Solution 1Recommended

Use the VLOOKUP Function to Auto-Populate the Department

The VLOOKUP function is the most straightforward method to search for a manager's name in one table and return their corresponding department to another cell.

VLOOKUP (Vertical Lookup) searches for a value in the leftmost column of a table and returns a value in the same row from a column you specify. This is ideal for pulling department names based on manager names.

Set up your reference table

Create or locate your reference data table (e.g., E2:F10). Ensure the Manager Names are in the first column (Column E) and Departments are in the second column (Column F).

Enter the VLOOKUP formula

Click the cell where you want the department to appear automatically (e.g., B2). Type the formula: =VLOOKUP(A2, E$2:F$10, 2, FALSE). In this formula, A2 is the cell where you type the manager's name.

Apply and drag the formula

Press Enter to apply the formula. Click the bottom-right corner of cell B2 and drag the fill handle down to apply this formula to the rest of the column.

Use the VLOOKUP Function to Auto-Populate the Department

Locking the table range: Adding dollar signs ($) to your lookup range (e.g., E$2:F$10) creates an absolute reference. This prevents the reference table area from shifting when you copy or drag the formula down to other rows.

Solution 2

Use INDEX and MATCH for Flexible Lookups

If your reference table is structured so that the manager's name is not in the first column, you can use a combination of INDEX and MATCH functions to retrieve the department.

Efficient Data Management with WPS Spreadsheet

Use WPS Spreadsheet to Effortlessly Look Up Data

WPS Spreadsheet fully supports advanced functions like VLOOKUP, XLOOKUP, and INDEX/MATCH. It provides a visual function wizard that makes automating data entry—like returning departments based on manager names—incredibly simple and completely free.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet that contains your employee and manager data tables.
2. Launch the Insert Function tool: Select the target cell, click the 'Formulas' tab on the top ribbon, and select 'Insert Function'. Search for 'VLOOKUP' and click OK.
3. Input the lookup arguments: In the dialog box, select your lookup value (the manager's name cell), the table array, enter '2' for the Col_index_num, type 'FALSE' in Range_lookup for an exact match, and click OK.

100% compatible with Microsoft Excel formulas and formats (.xlsx)Built-in visual Function Wizard to help you construct complex lookup formulas without errorsLightweight, fast performance even when handling large datasets and massive lookup tablesFree to download with a highly intuitive, tabbed user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP formula returning an #N/A error?

The #N/A error typically means the manager's name you are searching for does not exactly match any name in the lookup table. Check for spelling mismatches or hidden trailing spaces in the cells. Using the TRIM() function can help remove unwanted spaces from text.

Can I look up data if the manager reference table is on a different worksheet?

Yes. You can reference a table on another sheet by clicking on that sheet while building your formula, or by typing the sheet name followed by an exclamation mark before the range. For example: =VLOOKUP(A2, Sheet2!E$2:F$10, 2, FALSE).

What does the 'FALSE' argument mean at the end of the VLOOKUP formula?

The 'FALSE' argument forces the function to find an exact match for the manager's name. If you use 'TRUE' or leave the argument blank, Excel will look for an approximate match, which often yields incorrect results when searching for specific text strings like names.

How do I prevent the lookup range from changing when I copy the formula?

You need to make the table range an absolute reference. After selecting the lookup range in your formula, press the F4 key on your keyboard. This adds dollar signs to the column letters and row numbers (e.g., $E$2:$F$10), locking the range in place so it won't shift when copied.

Excel & Data Problems