Function Problems

How to Populate an Excel Specification Sheet from a Reference Table

Maira MehtabMaira Mehtab Nov 10, 2026 870 views

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.

How to Auto-Populate an Excel Specification Sheet from a Reference Table
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the destination cell

Click on the cell in your specification sheet where you want the specific data (like Product Name) to appear.

2
Enter the XLOOKUP formula

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.

3
Adapt the formula for other fields

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 the XLOOKUP Function to Retrieve Specifications
Absolute References: Using dollar signs ($) locks the cell reference for your input cell (e.g., $B$1) so the formula continues to point to the correct item number even if you copy the formula to other rows.
Boost Efficiency with WPS Spreadsheet

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. 1. Open your files: Launch WPS Spreadsheet and open the workbook containing your specification sheet and reference table.
  2. 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. 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. 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.
Fully compatible with Microsoft Excel file formats (.xlsx) and lookup formulas.Built-in error checking and formula evaluation tools for troubleshooting lookups.Lightweight software that processes large reference tables quickly without lagging.Free and highly intuitive user interface perfect for both beginners and experts.
microsoft office alternative - wps office

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.