Return Employee Names and Product Descriptions from IDs in Excel
Question details
Automatically return employee names and product descriptions that match IDs in a working workbook.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A separate reference workbook contains associate IDs and names, plus product SKUs and descriptions.
- Observed behavior
- The working workbook should display matching names and descriptions and refresh them when opened.
Check which columns contain the IDs and results in the reference workbook, and confirm that IDs use consistent text or number formats in both files. Save both workbooks and make sure the working workbook can access the reference file.
Use XLOOKUP with links to the reference workbook
Use this method when the reference lists must remain in a separate workbook.
The examples assume that Reference.xlsx has an Employees sheet with IDs in column A and names in column B, and a Products sheet with SKUs in column A and descriptions in column B. Adjust the sheet names and ranges to match your files.
In Excel, open the working workbook and Reference.xlsx. Check that the working sheet has associate IDs in column A and SKUs in column B.
In the first employee-name cell, enter =XLOOKUP(A2,'[Reference.xlsx]Employees'!$A$2:$A$1000,'[Reference.xlsx]Employees'!$B$2:$B$1000,"") and press Enter.
In the first product-description cell, enter =XLOOKUP(B2,'[Reference.xlsx]Products'!$A$2:$A$1000,'[Reference.xlsx]Products'!$B$2:$B$1000,"") and press Enter. Fill both formulas down for the remaining rows.
Save both files. When you reopen the working workbook, choose Update if Excel prompts you to update workbook links. If needed, use Data > Workbook Links or Edit Links to check and refresh the source.
On the Formulas tab, set Calculation Options to Automatic. Expand the formula ranges if the reference lists grow beyond row 1000.

Use Excel Tables for reference lists in the working workbook
Use this method if the reference lists can be kept in the same workbook as the formulas.
Return Names and Descriptions with WPS Spreadsheets
Use VLOOKUP in WPS Spreadsheets to match IDs against a reference workbook and refresh linked results when you open the working file.
- 1. Open the workbooks: In WPS Spreadsheets, open the working workbook and Reference.xlsx. Confirm that each reference sheet has IDs in column A and names or descriptions in column B.
- 2. Add the employee lookup: In the employee-name cell, enter =IFERROR(VLOOKUP(A2,'[Reference.xlsx]Employees'!$A$2:$B$1000,2,FALSE),"") and press Enter. Adjust the workbook, sheet, and range references as needed.
- 3. Add the product lookup: In the product-description cell, enter =IFERROR(VLOOKUP(B2,'[Reference.xlsx]Products'!$A$2:$B$1000,2,FALSE),"") and fill both formulas down.
- 4. Save and refresh: Save both workbooks. When reopening the working file, allow the link update if prompted and confirm that the reference workbook is still accessible.

Frequently Asked Questions
Why does XLOOKUP return a blank for an ID?
The formulas use an empty string when no match is found. Check that the ID exists in the reference range and that both values use the same text or number format.
Will the external formulas include new reference rows automatically?
The fixed ranges in the examples include rows 2 through 1000. If new records fall outside those ranges, edit the formulas to extend them.
What happens if I move the reference workbook?
The external links may no longer point to the file. In Excel, use Data > Workbook Links or Edit Links to check the source and change it to the new location.
What can I use if my Excel version does not have XLOOKUP?
Use VLOOKUP with FALSE for an exact match, provided the ID is in the first column of the lookup range. For example, =IFERROR(VLOOKUP(A2,Employees!$A$2:$B$1000,2,FALSE),"") returns a name from a local Employees sheet.




