How to Use XLOOKUP to Find Prices by Item ID in Excel
Open your Excel workbook (.xlsx) in Cue Office and use XLOOKUP to find each item ID in the price list. With an exact match, the formula returns the unit price from the same row.
Populate unit prices by item ID
In the Purchase Order worksheet, select cell C2. The ID to look up for this row is in A2. For IDs with leading zeros like 0042, format them as text in both sheets.
To test a single row first, paste just the first formula below. To fill C2:C6 all at once, copy all five formulas, select C2 in the grid, and paste.
=XLOOKUP(A2,'Price List'!$A$2:$A$5,'Price List'!$C$2:$C$5,"Not found",0) =XLOOKUP(A3,'Price List'!$A$2:$A$5,'Price List'!$C$2:$C$5,"Not found",0) =XLOOKUP(A4,'Price List'!$A$2:$A$5,'Price List'!$C$2:$C$5,"Not found",0) =XLOOKUP(A5,'Price List'!$A$2:$A$5,'Price List'!$C$2:$C$5,"Not found",0) =XLOOKUP(A6,'Price List'!$A$2:$A$5,'Price List'!$C$2:$C$5,"Not found",0)
XLOOKUP searches for the item ID in Price List!A2:A5 and returns the unit price from the same row in Price List!C2:C5. The ranges cover the same rows so each ID lines up with its price. Only the order’s ID reference changes from one formula to the next (A2, A3, and so on). The $ signs keep both price list ranges fixed.
The last argument, 0, asks for an exact match. If the ID is missing, the formula displays Not found. If you expect a match, find the ID in the price list first. Both sheets should store it as the same text value, such as 0042 with both leading zeros. When you add an item on row 6 of the price list, extend both ranges to include that row.
Check for missing IDs and test a price change
Save and reopen the workbook. In the purchase order, both rows for 0042 should show 4.25. The prices for 0047 and 0060 should be 8.50 and 1.80. The price list has no entry for 0099, so C4 should display Not found.
To test a price change, change Price List!C2 from 4.25 to 5.75 in a copy. Switch back to the purchase order and check that both prices for 0042 update to 5.75, while 0099 still shows Not found.
Ask Cue AI to fill in prices by item ID
If you regularly fill in prices on new purchase orders, give Cue AI the target range and lookup rules. Open a copy of the original workbook, select Cue AI on the Home tab, and send the prompt below.
Can you fill in the unit prices for this purchase order from the Price List and keep them up to date if the prices change? Match each item by its exact ID, including the leading zeros. Put the results in C2:C6 on Purchase Order, show “Not found” if an item isn’t listed, and leave the rest of the workbook as it is.
After Cue AI writes the formulas, save and reopen the copy. Select C2 and check the formula bar. In this example, Cue AI used VLOOKUP for an exact match and IFERROR to display Not found for a missing ID. Check that C4 shows that message and that the quantities and leading zeros are still there.
To check that the formulas Cue AI wrote recalculate, save a separate copy of the completed workbook. Change Price List!C2 to 5.75, then switch back to the purchase order. Both 0042 rows should update to 5.75, and 0099 should still show Not found. The original completed workbook keeps the 4.25 price.
Practice with a community repair purchase order
Show the complete example
Create two sheets named Purchase Order and Price List in one workbook. Before pasting, select column A in each sheet. On the Home tab, choose Text from the number format menu to preserve leading zeros. Then paste each table’s headers and rows into A1 of its sheet. Leave out the sheet names when you paste. Check that IDs such as 0042 and 0047 still show all four digits.
Purchase Order Item ID Quantity Unit price 0042 3 0047 2 0099 1 0042 4 0060 2 Price List Item ID Item Unit price 0042 Door hinge 4.25 0047 Sealant 8.50 0060 Wall anchor 1.80 0061 Brush 3.15
Use the unit prices to estimate a budget
Once the order has unit prices, ask Cue AI to add a line total column that multiplies Quantity by Unit price and lists the items that still need a quote. Check the first 0042 row by hand. Three units at 4.25 should cost 12.75. Leave 0099 out of the budget until you get its price.
When a supplier sends a new quote, open the PDF (.pdf) for free in Cue Office and check the item IDs and prices. Update the Price List sheet in your Excel (.xlsx) workbook so the purchase order formulas pick up the new prices. If the budget needs approval, you can use the free editing tools to prepare a Word (.docx) request or a PowerPoint (.pptx) briefing. Download Cue Office for free