How to Use an Excel Formula to Return a Department ID by Name in WPS Office
Question details
User needs a lookup formula to retrieve a department ID from another worksheet based on a department name.
- Product
- WPS Spreadsheets / Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook contains department names and department IDs, and the user wants to match a department name to its corresponding ID.
- Observed behavior
- The formula should return the correct department ID and blank or fallback when no match exists.
Before writing the formula, confirm which columns contain the department names and department IDs in the source worksheet. Also verify that the department names are unique or decide which match should be returned if duplicates exist.
Use XLOOKUP to Return a Department ID by Name
Best option if your WPS Office version supports XLOOKUP, because it is direct and handles missing matches cleanly.
XLOOKUP searches a lookup array and returns the corresponding value from a return array. It does not require the ID to be to the right of the name, and it can return a custom blank string when there is no match.
Click the cell where the department ID should appear, type =XLOOKUP(A2,Departments!$A:$A,Departments!$B:$B,"") and press Enter.
Replace A2 with the cell containing the department name, and replace Departments!$A:$A and Departments!$B:$B with your actual worksheet name and lookup/return columns.
Change the department name in A2 and confirm the formula returns the matching department ID or a blank cell when no match exists.
Use VLOOKUP when the ID is to the Right of the Name
Use this in older WPS Spreadsheets versions or when you prefer a simple vertical lookup.
Use INDEX and MATCH for a Flexible Two-Column Lookup
Use this when the return column can be left or right of the lookup column and you need compatibility with older versions.
Return Department IDs by Name in WPS Spreadsheets
WPS Spreadsheets lets you use XLOOKUP, VLOOKUP, or INDEX/MATCH to look up department IDs by name. Open your Excel workbook, enter the formula, and WPS handles the rest while keeping Excel compatibility.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click the WPS Spreadsheets icon, and open the Excel file that contains the department data.
- 2. Check your department data range: Open the worksheet that holds the department list and note which columns contain the department names and department IDs.
- 3. Enter the XLOOKUP formula: In the result cell, type =XLOOKUP(A2,Departments!$A:$A,Departments!$B:$B,"") and adjust the references to match your workbook.
- 4. Save in Excel-compatible format: Press Ctrl+S, then choose .xlsx to keep full Microsoft Excel compatibility, or save as a WPS file if needed.
Frequently Asked Questions
Does WPS Spreadsheets support XLOOKUP?
Recent WPS Office versions support XLOOKUP. If your version does not show it, use VLOOKUP or INDEX/MATCH, which are also supported in WPS Spreadsheets.
What should I do if the department name does not exist in the source sheet?
Wrap the lookup formula in IFERROR, or use the fourth argument of XLOOKUP, to return a blank value instead of an #N/A error.
Can I return a department ID when the ID column is to the left of the name column?
Yes. Use XLOOKUP or INDEX/MATCH because VLOOKUP normally cannot look left of its first selected column.
How do I reference another worksheet in WPS Spreadsheets?
Use the worksheet name followed by an exclamation mark and the range, such as Departments!$A:$A. If the sheet name contains spaces, wrap it in single quotes, for example 'Department List'!$A:$A.
