Use XLOOKUP or VLOOKUP to Add Prices from Another Excel Sheet
Question details
Retrieve each product's price from a price list on another worksheet and add it to the main table.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A main table contains product identifiers, and a separate worksheet contains the corresponding prices.
- Observed behavior
- The user wants the price column in the main table to display the matching price from the other worksheet.
Check which columns contain the product identifiers and prices on both sheets. The examples assume the main table's identifier is in A2, the output goes in D2, and the Price List sheet has identifiers in A2:A4 and prices in C2:C4.
Use XLOOKUP to Retrieve Prices from the Price List Sheet
Use this method in Microsoft 365 or Excel 2021 and later when XLOOKUP is available.
On the main worksheet, click D2, the first cell where you want to display a price.
In D2, enter =XLOOKUP(A2,'Price List'!$A$2:$A$4,'Price List'!$C$2:$C$4,"") and press Enter. The final argument returns a blank when no match is found.
Drag the fill handle from the bottom-right corner of D2 down the price column. Adjust the sheet name and ranges in the formula if your price list uses different locations.

Use VLOOKUP to Retrieve Prices in Older Excel Versions
Use VLOOKUP when your Excel version does not support XLOOKUP.
Add Prices from Another Sheet in WPS Spreadsheets
Use a cross-sheet VLOOKUP formula in WPS Spreadsheets to match product identifiers and bring prices into your main table.
- 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and select the main worksheet.
- 2. Enter the price formula: Click D2 and enter =IFERROR(VLOOKUP(A2,'Price List'!$A$2:$C$4,3,FALSE),""). Adjust the sheet name and ranges to match your workbook, then press Enter.
- 3. Fill and save: Drag D2's fill handle down the price column, then use File > Save to save the workbook.

Frequently Asked Questions
Why does the formula return a blank instead of a price?
The product identifier may not match an entry in the price list. Check A2 and the identifiers on the Price List sheet for extra spaces or different text and number formats. IFERROR also hides other VLOOKUP errors.
How do I use a different sheet name in the formula?
Replace 'Price List' with your actual worksheet name in each sheet reference. For a name containing spaces, keep the single quotes, as in 'Product Prices'!$A$2:$C$4.
Can VLOOKUP find a price to the left of the product identifier?
No. VLOOKUP searches the first column of its selected range and returns a value to its right. If available, use XLOOKUP with separate lookup and return ranges instead.
Why are there dollar signs in the price-list ranges?
The dollar signs make the ranges absolute, so references such as $A$2:$C$4 stay fixed when you drag the formula down. The A2 reference changes for each row.




