Function Problems

Return Employee Names and Product Descriptions from IDs in Excel

Steve KSteve K Nov 18, 2026 869 views

Question details

Automatically return employee names and product descriptions that match IDs in a working workbook.

Return Employee Names and Product Descriptions from IDs in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Open both workbooks

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.

2
Look up employee names

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.

3
Look up product descriptions

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.

4
Refresh links when opening

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.

5
Check calculation and ranges

On the Formulas tab, set Calculation Options to Automatic. Expand the formula ranges if the reference lists grow beyond row 1000.

Use XLOOKUP with links to the reference workbook
Keep the source accessible: Excel may add the source file path to the formulas. Moving or renaming the reference workbook can break the links, and link updates may depend on your Excel security settings.
Look up IDs in WPS Spreadsheets

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. 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. 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. 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. 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.
Match associate IDs and product SKUs to their descriptionsOpen and edit Microsoft Excel XLSX workbooksUse familiar spreadsheet formulas and menusWork with reference data in a separate workbook
microsoft office alternative - wps office

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.