Function Problems

Use XLOOKUP or VLOOKUP to Add Prices from Another Excel Sheet

John WilsonJohn Wilson Nov 18, 2026 870 views

Question details

Retrieve each product's price from a price list on another worksheet and add it to the main table.

Add Prices from Another Excel Sheet with XLOOKUP or VLOOKUP
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the price cell

On the main worksheet, click D2, the first cell where you want to display a price.

2
Enter the lookup formula

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.

3
Fill the formula down

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 XLOOKUP to Retrieve Prices from the Price List Sheet
Keep the lookup ranges aligned: The lookup and return ranges must cover corresponding rows. The dollar signs keep the price-list ranges fixed as you fill the formula down.
Look up prices in WPS Spreadsheets

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. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and select the main worksheet.
  2. 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. 3. Fill and save: Drag D2's fill handle down the price column, then use File > Save to save the workbook.
Look up product prices from another worksheetOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and fill handlesWork with spreadsheets using a free office suite
microsoft office alternative - wps office

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.