Function Problems

Automatically Fill Invoice Prices from an Excel Price List

Muhammad TalhaMuhammad Talha Nov 18, 2026 869 views

Question details

Create an invoice that returns a unit price automatically when an item number is entered.

Automatically Fill Invoice Prices from an Excel Price List
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the price list

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.

2
Create the invoice table

Create invoice columns named Item, Quantity, Unit Price, and Line Total. Select the range and choose Insert > Table.

3
Enter the lookup formula

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.

4
Calculate line totals

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.

Look Up Invoice Prices from an Excel Table
Check unmatched items: If the unit price shows Item not found, check that the item number exists in PriceList and that text and number formats match.
WPS Spreadsheets

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. 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. 2. Set up the invoice: On another sheet, enter the headers Item, Quantity, Unit Price, and Line Total in cells A1:D1.
  3. 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. 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.
Automatically return unit prices from an item price listOpen and edit Microsoft Excel XLSX filesCalculate line totals from quantity and unit priceUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.