How to Multiply Matching Values from Two Excel Sheets with XLOOKUP in WPS Office
Question details
The user needs to calculate a result in Sheet3 column I by matching a key in Sheet3 column H against Sheet1 and Sheet2 column H and multiplying the corresponding values from column I.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user is working with three sheets: Sheet3 holds the lookup keys, while Sheet1 and Sheet2 hold related values in column I.
- Observed behavior
- No calculated result appears until a cross-sheet XLOOKUP formula is entered; missing keys can produce #N/A errors.
Make sure the sheets are named exactly Sheet1, Sheet2, and Sheet3, or adjust the formula to match the actual sheet names. Confirm that your version of WPS Office Excel supports XLOOKUP.
Multiply Matching Values with XLOOKUP Across Sheet1 and Sheet2
Use this when the lookup key in Sheet3 column H exists in both Sheet1 and Sheet2 column H and both column I values should be multiplied.
XLOOKUP searches a lookup array and returns the corresponding value. Writing two XLOOKUP functions and multiplying them keeps the formula dynamic, so updates in Sheet1 or Sheet2 are reflected automatically.
In WPS Office Excel, open your workbook and select cell I2 in Sheet3 where the first calculated result will appear.
Type =XLOOKUP(H2,Sheet1!H:H,Sheet1!I:I)*XLOOKUP(H2,Sheet2!H:H,Sheet2!I:I) in cell I2 and press Enter.
Drag the fill handle from cell I2 down through the remaining rows in Sheet3 column I to apply the calculation to each key.
Wrap the XLOOKUP Multiplication with IFERROR for Missing Keys
Use this when some keys in Sheet3 may not exist in Sheet1 or Sheet2 and you want a friendly result instead of #N/A.
Calculate Cross-Sheet Products with XLOOKUP in WPS Office
Use WPS Office Excel to multiply matching values from two sheets without switching to a paid alternative. The built-in XLOOKUP function works across sheets, keeps formulas simple, and updates results as source data changes.
- 1. Open your workbook in WPS Office Excel: Launch WPS Office, click the Menu button in the top-left corner, choose Open, and browse to your .xlsx workbook.
- 2. Select Sheet3 cell I2: In the spreadsheet, click the Sheet3 tab and select cell I2 where the product should appear.
- 3. Enter the XLOOKUP multiplication formula: Type =XLOOKUP(H2,Sheet1!H:H,Sheet1!I:I)*XLOOKUP(H2,Sheet2!H:H,Sheet2!I:I) and press Enter.
- 4. Fill the formula down: Drag the fill handle from cell I2 down through the target rows to complete the calculation.
- 5. Save your workbook: Click the Save icon or press Ctrl+S to keep the formulas and results in your workbook.
Frequently Asked Questions
Can XLOOKUP multiply values from two different sheets in WPS Office?
Yes. In Sheet3 cell I2, enter =XLOOKUP(H2,Sheet1!H:H,Sheet1!I:I)*XLOOKUP(H2,Sheet2!H:H,Sheet2!I:I), then fill the formula down.
What happens if a key is missing from Sheet1 or Sheet2?
The multiplication formula returns #N/A because one XLOOKUP cannot find a match. Wrap the formula with IFERROR or provide an if_not_found value inside XLOOKUP to display a friendly message.
Can I multiply matching values from two different sheets without XLOOKUP in WPS Office?
Yes, you can use INDEX and MATCH as an alternative: =INDEX(Sheet1!I:I,MATCH(H2,Sheet1!H:H,0))*INDEX(Sheet2!I:I,MATCH(H2,Sheet2!H:H,0)).
Does the calculated product update automatically when source data changes?
Yes, WPS Office Excel recalculates XLOOKUP formulas automatically when a dependent cell in Sheet1 or Sheet2 changes. If calculation is set to manual, press F9 to update.
