Automatically Fill Invoice Prices from an Excel Price List
Question details
Create an invoice that returns a unit price automatically when an item number is entered.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- An invoice uses a separate item price list to populate prices and calculate line totals.
- Observed behavior
- Entering an item number should return its matching unit price without manual price entry.
Prepare a price list with one unique item number and price per row, and make sure item numbers use the same format on the invoice and price list. Check whether your Excel version supports XLOOKUP; if not, use the VLOOKUP alternative.
Look Up Invoice Prices from an Excel Table
Use a structured price list and a lookup formula to fill unit prices and calculate line totals.
Enter item numbers and prices in columns headed Item and Price. Select the range, choose Insert > Table, confirm that the table has headers, and name it PriceList on the Table Design tab.
Create invoice columns named Item, Quantity, Unit Price, and Line Total. Select the range and choose Insert > Table.
In the first data cell under Unit Price, enter =IF([@Item]="","",XLOOKUP([@Item],PriceList[Item],PriceList[Price],"Item not found")). If XLOOKUP is unavailable, use =IF([@Item]="","",IFERROR(VLOOKUP([@Item],PriceList[[Item]:[Price]],2,FALSE),"Item not found")); this alternative requires Item to be immediately before Price.
In the first data cell under Line Total, enter =IF(ISNUMBER([@[Unit Price]]),[@Quantity]*[@[Unit Price]],""). Enter an item number and quantity to test the invoice.

Build an Automatic Invoice Price Lookup in WPS Spreadsheets
Use VLOOKUP in WPS Spreadsheets to return a unit price from a separate price-list sheet and calculate each invoice line total.
- 1. Set up the price list: In WPS Spreadsheets, create a sheet named PriceList. Enter item numbers in A2:A100 and their prices in B2:B100.
- 2. Set up the invoice: On another sheet, enter the headers Item, Quantity, Unit Price, and Line Total in cells A1:D1.
- 3. Look up the unit price: In C2, enter =IF(A2="","",IFERROR(VLOOKUP(A2,PriceList!$A$2:$B$100,2,FALSE),"Item not found")).
- 4. Calculate and fill down: In D2, enter =IF(ISNUMBER(C2),B2*C2,""). Select C2:D2 and drag the fill handle down for additional invoice rows.

Frequently Asked Questions
Why does the formula show Item not found?
Check that the item number exists in the price list and is stored in the same format on both sheets. For example, a number stored as text may not match a numeric item number.
Can I use VLOOKUP if my Excel version does not support XLOOKUP?
Yes. Use VLOOKUP with FALSE for an exact match, and place the item number in the first column of the lookup range and the price in the second.
Will the invoice update when a price changes?
Yes. The lookup formula returns the current price from the linked price list when the workbook recalculates. Changing a price can therefore change existing invoice totals.
How do I prevent a missing price from causing a line-total error?
Use an ISNUMBER check in the line-total formula, such as =IF(ISNUMBER(C2),B2*C2,""). It leaves the total blank when C2 contains an error message instead of a price.




