Function Problems

How to Use Excel XLOOKUP Between Worksheets for Purchases and Pricing

Maira Mehtab Sep 22, 2026 875 views

Question details

The user needs to retrieve product descriptions, units, and individual costs from a 'Purchases' worksheet into a 'Pricing' worksheet using product codes.

Product: Excel
Device & OS: not provided
Scenario: Cross-referencing data between two different worksheets using a lookup function based on specific product codes.
Observed behavior: The user needs the correct XLOOKUP syntax to return data from corresponding columns on another sheet without errors.

Before you start

Ensure both the 'Pricing' and 'Purchases' worksheets are in the same workbook, and identify the exact column letters containing your lookup product codes and the return data.

Solution 1Recommended

Use XLOOKUP to Retrieve Data from the Purchases Worksheet

Apply specific XLOOKUP formulas to target different return columns while referencing the same lookup array on the Purchases sheet.

XLOOKUP is a powerful function that allows you to search for a value in one column and return a corresponding value from another, even across different worksheets. In this scenario, we use absolute references (like $F$2:$F$1000) for the lookup arrays so the formulas can be safely dragged down.

Enter the Product Description Formula

Click on cell B3 in the Pricing worksheet. Enter the formula =XLOOKUP(A3,Purchases!$F$2:$F$1000,Purchases!$D$2:$D$1000,"") and press Enter. This pulls the description from column D of the Purchases sheet.

Enter the Units Formula

Click on cell D3 in the Pricing worksheet. Enter the formula =XLOOKUP(A3,Purchases!$F$2:$F$1000,Purchases!$H$2:$H$1000,"") to pull the unit data from column H.

Enter the Individual Cost Formula

Click on cell E3 in the Pricing worksheet. Enter the formula =XLOOKUP(A3,Purchases!$F$2:$F$1000,Purchases!$J$2:$J$1000,"") to pull the cost data from column J.

Fill the Formulas Down

Select cells B3, D3, and E3. Click and drag the fill handle at the bottom-right corner of the selection down your column to apply the formulas to the rest of your product codes.

Formula Breakdown: The empty quotation marks "" at the end of the XLOOKUP formula ensure that if the product code isn't found, the cell remains blank instead of showing an #N/A error.

Efficient Data Management

Use XLOOKUP Seamlessly in WPS Spreadsheet

WPS Spreadsheet offers full support for advanced functions like XLOOKUP, allowing you to easily pull data across multiple sheets. It is fully compatible with Microsoft Excel formulas and functions.

1. Open your Workbook in WPS Office: Launch WPS Office and open your .xlsx file containing the Pricing and Purchases sheets.
2. Insert the XLOOKUP Function: Select your target cell in the Pricing sheet, type =XLOOKUP(, and select your lookup value.
3. Select Cross-Sheet Arrays: Click the Purchases sheet tab to select your lookup array, type a comma, then select the corresponding return array.
4. Apply and Auto-Fill: Press Enter to execute the formula, then use the fill handle to drag it down the column to automatically populate your data.

100% compatible with Microsoft Excel formats (.xlsx)Full support for modern functions like XLOOKUP and VLOOKUPLightweight, fast, and completely free to useIntuitive interface for complex cross-sheet calculations

microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP return an #N/A error across sheets?

An #N/A error occurs if the exact lookup value doesn't exist in the lookup array. You can prevent this by adding a fourth argument to your XLOOKUP formula, such as "" or "Not Found", to handle missing data gracefully without breaking your spreadsheet's appearance.

Do I need to use absolute references in my XLOOKUP arrays?

Yes, it is highly recommended. Using absolute references, designated by dollar signs like $F$2:$F$1000, prevents the lookup and return ranges from shifting downward when you copy or drag the formula to other rows.

Is XLOOKUP better than VLOOKUP for cross-sheet references?

Yes. XLOOKUP is significantly more flexible than VLOOKUP because it allows you to look up values to the left of the lookup column, defaults to an exact match automatically, and handles missing data natively without needing a separate IFERROR wrapper.

Can I use XLOOKUP across entirely different workbooks?

Yes, you can use XLOOKUP to pull data from another workbook. However, to prevent reference errors, both workbooks should ideally be open when setting up the formula, and Excel will automatically include the full path and workbook name in the formula reference.

Excel & Data Problems