Function Problems

How to Assign Names Based on Selected Positions in Excel (XLOOKUP)

Rana Garcia Sep 28, 2026 869 views

Question details

The user wants to automatically display specific employee names in target assignment cells based on the positions selected from a drop-down list.

How to Assign Employee Names Based on Selected Positions in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating an automated employee roster or scheduling sheet where choosing a job role from a drop-down dynamically fetches and assigns the corresponding staff member's name.
Observed behavior: Employee names need to dynamically populate the assignment sections when their corresponding roles are selected, and the formula needs to scale to multiple employee groups.

Before you start

Verify that your version of Excel supports the XLOOKUP function (Excel 365, Excel 2021, or newer), and ensure that your Data Validation drop-down lists are correctly set up to prevent typing errors.

Solution 1Recommended

Use XLOOKUP to Dynamically Match Names to Selected Positions

The most efficient way to assign names based on a selected position is to use Excel's XLOOKUP function, which directly searches for the selected position and returns the matching employee name.

XLOOKUP allows you to search for a value in one column and return a corresponding value from another column, even if the return column is to the left of the search column. It also handles arrays, allowing one formula to populate an entire section.

Verify position match

Ensure that the positions listed in your Data Validation drop-down menu exactly match the spelling and format of your reference position list.

Enter the XLOOKUP formula

Select the destination cell where the name should appear (for example, K8) and type the formula: =XLOOKUP(J8:J14, D5:D9, C5:C9, "Not Assigned").

Customize the formula ranges

Adjust J8:J14 to the cells containing your selected drop-down positions. Change D5:D9 to the reference column containing all available positions, and C5:C9 to the reference column containing the corresponding employee names.

Extend to additional groups

To apply this logic to other layout sections (like rows 11 through 20 or columns E and F), copy the formula and update the lookup value and array ranges to point to the new employee groups.

Use XLOOKUP to Dynamically Match Names to Selected Positions

Handling Empty Selections: The fourth argument in the XLOOKUP formula ("Not Assigned") acts as a built-in error handler. If a position drop-down is left blank or a match isn't found, the cell will elegantly display "Not Assigned" instead of an #N/A error.

Advanced Data Management in WPS

Effortlessly Automate Employee Assignments with WPS Spreadsheet

WPS Spreadsheet fully supports modern array functions like XLOOKUP, making it incredibly easy to automate your staff rosters, link drop-down lists, and build dynamic scheduling dashboards without complex workarounds.

1. Open your roster file: Launch WPS Spreadsheet and open your existing .xlsx employee assignment tracker or create a new workbook.
2. Create your drop-down lists: Highlight your target cells, navigate to the Data tab, click on Data Validation, and select 'List' to input your available positions.
3. Apply the XLOOKUP formula: Click the cell next to your drop-down menu and type your =XLOOKUP() formula to instantly link the assigned names to the chosen job roles.

Fully compatible with Microsoft Excel (.xlsx) file formats, ensuring advanced functions like XLOOKUP execute perfectly.Built-in intuitive Data Validation tools for easily setting up and managing dynamic drop-down lists.Lightweight, fast-loading, and completely free to use for comprehensive daily data management tasks.Tabbed viewing interface allows you to reference and manage multiple department rosters in a single window.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP formula return an #N/A error?

This error usually occurs when the position selected in the drop-down list doesn't perfectly match the lookup array (e.g., accidental trailing spaces). It can also happen if your lookup array and return array are not exactly the same size.

Can I use VLOOKUP instead of XLOOKUP for this task?

Yes, but VLOOKUP has a strict limitation: the value you are searching for (the position) must be in the leftmost column of your reference table. If your layout places names to the left of the positions, you must use XLOOKUP or an INDEX/MATCH combination.

How can I reference employee names located on a different worksheet?

You can pull data from another sheet by adding the sheet name followed by an exclamation mark before the cell range. For example: =XLOOKUP(J8, 'Staff List'!D5:D9, 'Staff List'!C5:C9, "Not Assigned").

What if my version of Excel doesn't support XLOOKUP?

If you are using an older version of Excel (like Excel 2016 or 2019), you can achieve the exact same result using the INDEX and MATCH functions. The equivalent formula would look like: =IFERROR(INDEX(C5:C9, MATCH(J8, D5:D9, 0)), "Not Assigned").

Excel & Data Problems