Function Problems

How to Link Prices and Inventory Between Excel Workbooks

Maira Mehtab Sep 27, 2026 874 views

Question details

The user needs to retrieve product prices by SKU from one workbook and automatically combine them with inventory quantities in a separate workbook to calculate the total stock value.

Product: Microsoft Excel
Device & OS: Desktop
Scenario: Automating inventory value calculations across multiple files to avoid manual copy-pasting of updated pricing data.
Observed behavior: The user is looking for an automated lookup formula rather than a manual copy-and-paste process to dynamically link SKU prices and quantities.

Before you start

Ensure both the pricing workbook and the inventory workbook are saved locally on your desktop and open them simultaneously to easily reference cells between them while building your formula.

Solution 1Recommended

Use XLOOKUP to Retrieve and Calculate Data Across Workbooks

Using the XLOOKUP function is the most efficient way to match SKUs between workbooks, fetch the corresponding price, and multiply it by the stock quantity in one step.

Lookup formulas allow you to search for a specific identifier, such as a SKU number, in a separate workbook and return its related price value. By embedding this formula directly into a multiplication equation, you can instantly calculate the total value of your current stock.

Open Both Workbooks

Open your primary inventory workbook and the secondary pricing workbook (e.g., Prices.xlsx) in Excel.

Select the Target Cell

In your inventory workbook, click on the cell where you want the total stock value for the first item to appear.

Enter the XLOOKUP Formula

Type the beginning of your formula: =XLOOKUP(A2, where A2 is the cell containing the SKU in your current inventory workbook.

Select the Cross-Workbook References

Switch to the Prices.xlsx workbook, highlight the SKU column for the lookup array, type a comma, then highlight the Price column for the return array.

Multiply by Inventory Quantity

Complete the formula by multiplying the XLOOKUP result by your local stock quantity. Your final formula should look like: =XLOOKUP(A2,[Prices.xlsx]Sheet1!$A:$A,[Prices.xlsx]Sheet1!$B:$B)*B2.

Apply to Remaining Rows

Press Enter to calculate the value, then click and drag the fill handle at the bottom-right corner of the cell down to apply this formula to the rest of your inventory list.

Alternative Formula for Older Versions: If you are using an older version of Excel that does not support XLOOKUP, you can use VLOOKUP instead. The formula structure would be: =VLOOKUP(A2,[Prices.xlsx]Sheet1!$A:$B,2,FALSE)*B2.

Manage Inventory Seamlessly

Link Data Across Workbooks with WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup formulas like XLOOKUP and VLOOKUP, allowing you to link inventory and pricing data across multiple workbooks effortlessly and securely.

1. Launch WPS Spreadsheet: Open WPS Office and start the WPS Spreadsheet application.
2. Open Your Files: Open both your inventory list workbook and your pricing list workbook in separate tabs.
3. Start the Formula: In your target inventory cell, type =XLOOKUP( and select the local SKU cell you want to search for.
4. Reference the Pricing File: Navigate to the pricing workbook tab to select the SKU lookup array and the price return array, then add * followed by your local quantity cell.
5. Calculate and Fill: Press Enter to instantly calculate your total inventory value, then drag the cell downwards to fill the rest of the column.

Fully compatible with Microsoft Excel (.xlsx) formats and cross-workbook referencesNative support for advanced reference functions including XLOOKUP and VLOOKUPEasily handle large datasets across multiple open workbooks without performance lagsFree to download and highly intuitive for everyday office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Do both Excel workbooks need to be open for XLOOKUP to work?

No, XLOOKUP will continue to fetch the correct data and calculate the values even if the source workbook is closed. However, having both workbooks open initially makes it much easier to point and click to select ranges when writing the formula.

Why does my cross-workbook formula show a #REF! error?

A #REF! error typically occurs if the referenced workbook, worksheet, or specific column was deleted or moved. Ensure the file path, document name, and sheet names of your source file remain unchanged to keep the link intact.

Can I update the prices in the source workbook without breaking the link?

Yes. As long as the file name, sheet name, and column structures remain identical, any updated price values in the source workbook will automatically reflect in the calculations within your inventory workbook the next time it recalculates.

Excel & Data Problems