On this page
A very common task: you have a packing list and an invoice, and you need to check that every item appears on both. Lookup formulas do this in seconds.
The old way: VLOOKUP
Say the invoice sits on a sheet called Invoice, with item codes in column A and amounts in column D. On your packing list, you want the amount next to each item code in A2:
=VLOOKUP(A2, Invoice!A:D, 4, FALSE)The 4 means "take the value from the 4th column of that range". The FALSE means "exact match only". People forget the FALSE and get wrong answers, which is the most common VLOOKUP mistake.
VLOOKUP breaks if someone inserts a column in the invoice sheet, because the 4 now points to the wrong column.
The better way: XLOOKUP
=XLOOKUP(A2, Invoice!A:A, Invoice!D:D, "Not on invoice")You pick the column to search, the column to return, and what to show if nothing is found. There is no column number to break, and exact match is the default.
XLOOKUP is available in Excel 2021 and Microsoft 365. It is not in Excel 2019 or older. If you have an older version, use the next option.
The option that works everywhere: INDEX and MATCH
=INDEX(Invoice!D:D, MATCH(A2, Invoice!A:A, 0))MATCH finds the row, and INDEX returns the value from that row. The 0 means exact match. If the item is missing, you get #N/A, which you can hide with IFERROR.
Finding the missing items
To flag items that are on the packing list but not on the invoice, add a check column:
=IF(COUNTIF(Invoice!A:A, A2)=0, "Missing on invoice", "OK")Then do the same in the other direction, to find items on the invoice that are not on the packing list. This two-way check catches most documentation errors before the shipment leaves.
When the lookup says "not found" but you can see the item
This almost always means one of two problems:
- Hidden spaces. "AB-100 " with a space at the end is not the same as "AB-100".
- Numbers stored as text. The code 10045 as a number does not match "10045" as text.
Both are easy to fix. See how to clean messy data in Excel.