How to find missing values between two Excel lists
Find which product codes are missing from either Excel list. Compare the columns directly and review shared codes and unmatched entries in one result.
To find order codes missing from the receipts, connect the two code columns and choose Compare values in JOIN SHEET. The result shows shared codes and entries found in only one list. You do not need to sort the files into the same order first.
Look across the list, not at the same cell position
Suppose Purchase orders contains P10, P20 and P30, while Receipts contains P20, P30 and P40. Comparing the first cell in each sheet tells you that P10 differs from P20. It does not tell you whether P10 appears elsewhere in the receipts.
The walkthrough uses fictional order and receipt lists. Excel formulas and conditional formatting can also help with this check. In JOIN SHEET, the comparison produces a separate table you can review and save.
Compare the two code columns
- Choose Add files in the dashboard and load both sheets. Alternatively, copy data including its headers and use Paste copied data.
- Drag the connection point beside Product code in Purchase orders to Receipt code in Receipts. The column names can differ.
- In Choose an operation, select Compare values. This comparison line is all you need to check which list contains each code.
- Read Comparison result alongside the two code columns in Result preview.
Check the four expected results
| Code | Comparison result | What to investigate |
|---|---|---|
| P10 | First sheet only | No matching code in Receipts |
| P20 | In both sheets | Present in both lists |
| P30 | In both sheets | Present in both lists |
| P40 | Second sheet only | No matching code in Purchase orders |
Check that Purchase orders is the first sheet before interpreting the labels. A missing match is not automatically an error: the delivery may still be due, or the files may cover different dates.
Does this also check repeated codes or changed prices?
If a code appears twice on one side and once on the other, the extra occurrence can appear as unmatched. Check those counts separately from the quantities ordered or received: a shared code tells you where it appears, not how many units were delivered.
For price changes, first join on a stable product code, then compare the price columns. See how to compare changed values by ID. Once you have checked the result, you can save the full result to Excel.
Try it with your own files
Start with JOIN SHEET ↗Use a desktop computer for Excel and CSV work.
Opens in a new tab without replacing your current work.
Example last verified:
Sources
How to compare two columns and find differences in Excel? — Super User
The walkthrough uses fictional order and receipt data to address the question above.
