How to Populate an Excel Specification Sheet from a Reference Table
Question details
The user wants to automatically fill in product details like name, description, and item number on a specification sheet based on an item number entered in a specific cell.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating an automated specification sheet that retrieves data from a master reference table.
- Observed behavior
- The user needs to establish a link between an input cell and a data table so that entering an item number instantly displays all corresponding product specifications without manual data entry.
Ensure your master reference table is organized with the lookup value (e.g., item number) in a single column without duplicates, and verify that the data types between your input cell and reference table match exactly.
Use the XLOOKUP Function to Retrieve Specifications
XLOOKUP is the most versatile and modern lookup function, allowing you to search in any direction and handle missing data easily.
XLOOKUP replaces older lookup functions by allowing you to specify exactly which column to search and which column to return. It does not break if new columns are added to your reference table and includes a built-in feature to handle errors when an item isn't found.
Click on the cell in your specification sheet where you want the specific data (like Product Name) to appear.
Type the formula =XLOOKUP($B$1, Data!$A:$A, Data!$B:$B, "Not Found") into the formula bar. Replace $B$1 with the cell reference where you type the item number. Replace Data!$A:$A with the column in your reference table containing the item numbers, and Data!$B:$B with the column containing the product names.
Press Enter to apply the formula. Copy this formula to other specification fields (like description or price) and change only the return array (e.g., Data!$C:$C) to point to the correct column in your reference table.

Use VLOOKUP for Standard Data Retrieval
VLOOKUP is a classic method for pulling data when your lookup value is in the leftmost column of your reference table.
Use Word Mail Merge with Excel Data for Advanced Formatting
If you need a heavily formatted, printable specification sheet, using Excel as the database and Word for the layout is highly effective.
Easily Manage Reference Tables and Lookups in WPS Spreadsheet
WPS Spreadsheet fully supports advanced formulas like XLOOKUP, VLOOKUP, and INDEX/MATCH, allowing you to seamlessly auto-populate complex specification sheets. It offers a fast, user-friendly interface that handles large datasets with ease.
- 1. Open your files: Launch WPS Spreadsheet and open the workbook containing your specification sheet and reference table.
- 2. Access the function library: Click on the destination cell, go to the 'Formulas' tab, and select 'Insert Function' to search for XLOOKUP or VLOOKUP.
- 3. Define function arguments: Follow the on-screen function arguments dialog to intuitively select your lookup value, lookup array, and return array without manually typing complex syntax.
- 4. Apply across your sheet: Press OK to generate the result, then use the fill handle to drag and copy the formulas across the rest of your specification template.

Frequently Asked Questions
Why is my VLOOKUP formula returning an #N/A error on the specification sheet?
An #N/A error occurs when the formula cannot find the exact lookup value (like the item number) in the reference table. Ensure there are no extra spaces in the cells, the data types match (e.g., formatting both as text or both as numbers), and the reference range covers the actual data.
Can I pull data from a reference table located in a completely different workbook?
Yes, both VLOOKUP and XLOOKUP can reference external workbooks. Ensure the external workbook is open when initially creating the formula. The syntax will include the file name in brackets, such as ='[MasterData.xlsx]Sheet1'!$A$1:$C$100.
How do I populate an image based on an item number in Excel?
Standard lookup formulas only retrieve text and numbers. To pull dynamic images into a specification sheet, you must use a combination of the INDEX and MATCH functions inside a defined Name Range, and apply that name to a Linked Picture.
What is the main advantage of using XLOOKUP over VLOOKUP for spec sheets?
XLOOKUP is much more robust; it allows you to search for data to the left of your lookup column, doesn't break if you insert or delete columns in your reference table, and features a built-in argument to handle 'not found' scenarios without needing an additional IFERROR function.




