How to Use XLOOKUP in WPS Spreadsheets to Retrieve Part Prices from Another Worksheet
Question details
The user needs to link part IDs to prices stored in another worksheet or workbook and calculate purchase order totals using quantity, pricing type, and optional markup.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user maintains a purchase order sheet and needs to pull part prices from a separate worksheet or workbook by part ID, then calculate totals including quantity and optional markup.
- Observed behavior
- They need a reliable lookup formula that works in WPS Spreadsheets and, where required, remains compatible with Microsoft Excel.
Before creating the formula, open both the destination and source workbooks in WPS Spreadsheets and save the source workbook with its final name and location. Confirm the part IDs are formatted the same way in both sheets, such as text or numbers.
Use XLOOKUP in WPS Spreadsheets to retrieve part prices from another worksheet
Use this modern lookup function when your WPS Spreadsheets version supports XLOOKUP and you want a robust, exact-match formula with built-in not-found handling.
XLOOKUP searches a lookup array and returns the matching value from a return array. It can reference ranges in another worksheet in the same workbook and in a saved external workbook; WPS Spreadsheets adds the file path automatically when the source workbook is open.
In WPS Spreadsheets, open the purchase order workbook and the source workbook that contains part IDs and prices. Save the source workbook first so its path can be referenced reliably.
Click the cell where the part price should appear, such as the first row of the purchase order price column.
Type =XLOOKUP(A2,[Source.xlsx]Parts!$A:$A,[Source.xlsx]Parts!$B:$B,"Not found") and replace A2, Source.xlsx, Parts, $A:$A, and $B:$B with your actual part ID cell, workbook name, worksheet name, lookup column, and price column.
In the total column, enter a formula such as =C2*D2*(1+E2) where C2 is quantity, D2 is the returned price, and E2 is the optional markup rate, then fill the formulas down.
Use VLOOKUP or INDEX and MATCH when sharing with older Excel versions
Choose this method if some users still work in Excel 2019 or earlier and cannot use XLOOKUP, or if you need maximum compatibility.
Retrieve Part Prices Across Worksheets with WPS Spreadsheets
Use XLOOKUP, VLOOKUP, or INDEX and MATCH in WPS Spreadsheets to pull part prices from another worksheet or saved workbook, then calculate purchase order totals with optional markup. WPS Spreadsheets keeps formulas and cross-workbook links compatible with Excel workbooks.
- 1. Download and install WPS Office: Download WPS Office free from the official WPS site, install it, and open WPS Spreadsheets.
- 2. Open your purchase order and price source workbook: Open both the destination workbook and the source workbook that contains part IDs and prices, then save the source workbook.
- 3. Apply XLOOKUP or a compatible lookup formula: In the price cell, enter =XLOOKUP(A2,[Source.xlsx]Parts!$A:$A,[Source.xlsx]Parts!$B:$B,"Not found") or use VLOOKUP/INDEX-MATCH for older Excel compatibility.
- 4. Calculate totals and save: Add the quantity and markup formula, fill down, and save the workbook in WPS Spreadsheets as a standard .xlsx file.
Frequently Asked Questions
Does WPS Spreadsheets support XLOOKUP?
Yes, current versions of WPS Spreadsheets support XLOOKUP. If your version does not, use VLOOKUP or INDEX and MATCH to achieve the same cross-sheet lookup.
Why does my external workbook formula show #REF! or ask for an update?
This usually means the source workbook was moved, renamed, or not saved. Open both workbooks, save the source workbook first, and keep it in a stable folder; then edit the formula or refresh links.
Can XLOOKUP pull prices from a worksheet in another workbook?
Yes. Open both workbooks, select the lookup and return ranges from the source workbook, or type the workbook and worksheet reference, and WPS Spreadsheets adds the file path automatically.
What formula should I use if some users have Excel 2019?
Use VLOOKUP with FALSE for an exact match, or INDEX and MATCH. These work in both WPS Spreadsheets and older Excel versions.
How do I calculate purchase order totals with markup?
After returning the price, multiply quantity by price and by 1 plus the markup rate. For example, =C2*D2*(1+E2) where C2 is quantity, D2 is price, and E2 is markup.
