How to Add a Department to an Excel Report Using XLOOKUP
Question details
The user needs to retrieve department names from a master workbook and add them to an extracted report by matching Employee IDs.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Merging data from two separate workbooks (a master staff list and an hours logged report) based on a common identifier.
- Observed behavior
- The extracted report lacks department information, requiring a lookup function or query tool to automatically pull the correct department for each employee.
Ensure both your master workbook and the extracted report are open in your spreadsheet software to make selecting your data ranges easier and to prevent broken link errors.
Use the XLOOKUP Function to Match Data
XLOOKUP is the most efficient and modern way to retrieve data from another sheet or workbook based on a matching identifier.
The XLOOKUP function allows you to search for an Employee ID in the master workbook and return the corresponding Department name to your current report. It replaces older functions like VLOOKUP and provides built-in error handling.
Open both the master workbook and your extracted report. In the extracted report, click on the first empty cell in your new 'Department' column (for example, C2).
Type `=XLOOKUP(B2,` into the cell, where B2 is the cell containing the Employee ID in your current report.
Switch to your master workbook window, click the column header for the Employee ID (e.g., Column B) to select the entire column, and type a comma. The formula will look similar to `=XLOOKUP(B2,[Master.xlsx]Sheet1!$B:$B,`.
Still in the master workbook, click the column header for the Department (e.g., Column C) to select it.
Type a comma, add the text `"Not found"` to handle any IDs that do not exist in the master sheet, close the parentheses, and press Enter. The final formula will look like `=XLOOKUP(B2,[Master.xlsx]Sheet1!$B:$B,[Master.xlsx]Sheet1!$C:$C,"Not found")`.
Click the cell with the completed formula and double-click the small square (fill handle) in the bottom-right corner to copy the formula down to the rest of the rows in your report.
Use Power Query for Recurring Imports
If you need to perform this matching task regularly, Power Query can automate the process of merging the report and master table.
Match and Merge Data Effortlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like XLOOKUP, VLOOKUP, and dynamic arrays, making it incredibly easy to merge data from multiple reports. It offers a fast, lightweight, and highly compatible workspace for handling complex data sets.
- 1. Open your data sets: Launch WPS Spreadsheet and open both the master staff list and the exported hours report.
- 2. Insert the XLOOKUP formula: Navigate to the Formulas tab, click 'Insert Function', search for XLOOKUP, or directly type `=XLOOKUP(` into your target cell.
- 3. Define your matching criteria: Follow the tooltip prompts to select the lookup value (Employee ID in the report), lookup array (Employee IDs in the master), and return array (Departments in the master).
- 4. Analyze the completed report: Drag the formula down to populate all rows. You can now use WPS Spreadsheet's PivotTable feature to summarize logged hours by each department.
Frequently Asked Questions
Why is my XLOOKUP formula returning a #N/A error?
The #N/A error indicates that the specific Employee ID from your report cannot be found in the master workbook's ID column. This can happen if the ID is missing, or if there are formatting mismatches (e.g., one is formatted as text and the other as a number) or hidden trailing spaces.
Can I use VLOOKUP instead of XLOOKUP to find the department?
Yes, you can use VLOOKUP, provided that the Employee ID column is located to the left of the Department column in the master workbook. However, XLOOKUP is generally preferred because it does not require counting column index numbers, defaults to an exact match, and works regardless of column order.
How do I fix the #REF! error when I close my master workbook?
Sometimes cross-workbook formulas can break or slow down processing when the source file is closed. To prevent this, select your populated Department column, press Ctrl+C to copy, right-click the same selection, and choose 'Paste Special > Values'. This converts the formulas into static text so they no longer rely on the master workbook.
