Excel Formulas to Compare Two Inventory Logs and Find Missing SKUs

Reconciling discrepant inventory logs to identify missing SKU numbers can stall supply chain operations and drain valuable administrative hours. While standard enterprise software integrations and inventory budget allocations serve as your typical baseline resources, manual cross-referencing remains highly prone to human error.

Leveraging a targeted Excel formula grants your team immediate, automated visibility into stock anomalies. As a stipulation, both inventory logs must share identical cell formatting to ensure accurate comparison. For instance, utilizing the =ISNA(MATCH(A2, Sheet2!A:A, 0)) formula serves as a robust proof-of-concept for matching records. Below, we will explore the exact step-by-step configuration to seamlessly execute this audit.

Managing inventory is one of the most critical aspects of running a successful retail, e-commerce, or manufacturing business. Yet, anyone who has ever managed a warehouse knows that data discrepancies are inevitable. You might pull a master inventory report from your Enterprise Resource Planning (ERP) system, only to find it doesn't quite match the physical count log compiled by your warehouse team. Or perhaps you are reconciling a supplier's invoice list against your internal receiving records.

When you are dealing with hundreds or thousands of Stock Keeping Units (SKUs), comparing these logs manually is not just tedious-it is a recipe for expensive errors. Missing SKUs can lead to phantom inventory, unfulfilled orders, and inaccurate financial reporting. Fortunately, Microsoft Excel offers several robust formulas to automate this reconciliation process, allowing you to identify missing SKUs in seconds.

In this comprehensive guide, we will explore the best Excel formulas to compare two inventory logs, ranging from modern functions like XLOOKUP and FILTER to classic compatibility workhorses like VLOOKUP and MATCH.

Setting Up the Scenario

Before diving into the formulas, let us establish a standard scenario. Imagine you have two inventory logs in the same Excel workbook:

• Log A (Master List): This is your system's source of truth, located in a sheet named MasterLog. The SKUs are listed in column A (starting from cell A2 down to A1000).
• Log B (Physical Audit): This is the physical count sheet compiled from a warehouse walkthrough, located in a sheet named PhysicalAudit. The SKUs actually found are listed in column A of that sheet.

Our goal is to compare the MasterLog against the PhysicalAudit and flag any master SKUs that are completely missing from the physical audit log.

Method 1: The Modern & Elegant Way (XLOOKUP)

If you are using Microsoft 365, Excel 2021, or Excel for the Web, XLOOKUP is the most efficient function for this task. Unlike its predecessor VLOOKUP, XLOOKUP is highly flexible, defaults to exact matches, and features a built-in argument for handling missing values (errors) without needing to nest additional functions.

The Formula:

=XLOOKUP(A2, PhysicalAudit!A:A, PhysicalAudit!A:A, "Missing in Audit")

How It Works:

1. A2 (Lookup Value): This is the individual SKU from your Master List that you want to check.
2. PhysicalAudit!A:A (Lookup Array): Excel searches for the master SKU inside the entire SKU column of the Physical Audit sheet.
3. PhysicalAudit!A:A (Return Array): If Excel finds the SKU, it returns the value from this column (confirming its presence).
4. "Missing in Audit" (If Not Found): This is the magic of XLOOKUP. If the SKU cannot be found in the physical audit, instead of displaying an ugly #N/A error, Excel will cleanly return the text "Missing in Audit".

By dragging this formula down next to your Master List, you instantly get a clear status column showing either the validated SKU or a highly visible "Missing in Audit" flag.

Method 2: The Boolean Check (ISNUMBER + MATCH)

Sometimes you don't need to return the SKU itself; you just want a clean "True/False" or "Found/Missing" status indicator. Combining the MATCH and ISNUMBER functions is an exceptionally clean, lightweight way to achieve this.

The Formula:

=IF(ISNUMBER(MATCH(A2, PhysicalAudit!A:A, 0)), "Found", "Missing")

How It Works:

• MATCH(A2, PhysicalAudit!A:A, 0): This looks up the SKU in A2 within the physical audit column. If it finds it, it returns its relative row position (a number, e.g., 45). If it does not find it, it returns an #N/A error.
• ISNUMBER(...): This evaluates the result of the MATCH function. If MATCH successfully found a row number, ISNUMBER returns TRUE. If MATCH returned an error, ISNUMBER returns FALSE.
• IF(...): Finally, the IF statement translates those raw TRUE/FALSE results into user-friendly labels: "Found" or "Missing".

This method is highly scalable and executes incredibly fast, making it ideal for massive datasets containing tens of thousands of rows.

Method 3: Legacy Compatibility (VLOOKUP + IFNA)

If you are working in an environment with older versions of Excel (Excel 2013, 2016, or 2019), XLOOKUP will not be available. In this case, you can combine the classic VLOOKUP with IFNA to achieve the exact same clean visual result.

The Formula:

=IFNA(VLOOKUP(A2, PhysicalAudit!A:A, 1, FALSE), "Missing")

How It Works:

• VLOOKUP(A2, PhysicalAudit!A:A, 1, FALSE): Excel looks for the SKU in A2 inside the first column of the Physical Audit sheet. The 1 tells Excel to return the value from that same column if found, and FALSE enforces an exact match.
• IFNA(..., "Missing"): If VLOOKUP cannot find the SKU, it naturally outputs #N/A. The IFNA wrapper catches that specific error and replaces it with the word "Missing". If the SKU is found, it simply displays the SKU code.

Method 4: Extracting a Dynamically Filtered List (FILTER)

While flagging missing SKUs row-by-row is helpful, it still requires you to manually filter or scroll through your master sheet to see the discrepancies. What if you want to automatically generate a clean, isolated list of only the missing SKUs on a third sheet?

By using the dynamic array function FILTER, you can create a live dashboard that automatically displays missing inventory items.

The Formula:

=FILTER(MasterLog!A2:A1000, ISNA(MATCH(MasterLog!A2:A1000, PhysicalAudit!A2:A1000, 0)))

How It Works:

• MasterLog!A2:A1000: This is the array of data we want to extract from (our list of master SKUs).
• MATCH(MasterLog!A2:A1000, PhysicalAudit!A2:A1000, 0): Excel compares every single master SKU against the physical audit list.
• ISNA(...): This evaluates to TRUE for every SKU that returns an #N/A error (meaning it is missing from the audit log).
• FILTER(...): The FILTER function spits out only the rows where the ISNA evaluation was TRUE. Because this is a dynamic array formula, the results will automatically "spill" down the column, updating in real-time as your data changes.

Pro Tip: Use Conditional Formatting to Highlight Missing SKUs

If you prefer a highly visual representation directly within your Master List, you can use Excel's Conditional Formatting engine to automatically highlight missing SKUs in red. Here is how to set it up:

1. Select the SKU column in your MasterLog sheet (e.g., A2:A1000).
2. Go to the Home tab, click on Conditional Formatting, and select New Rule.
3. Choose "Use a formula to determine which cells to format".
4. Enter the following formula:

   =ISNA(MATCH(A2, PhysicalAudit!A:A, 0))

5. Click the Format button, choose a light red fill color under the Fill tab, and click OK.

Now, any SKU in your master list that is absent from your physical audit sheet will instantly flash red, allowing you or your team to immediately target those items for recounts.

Summary of Methods

Conclusion

Inventory audits do not have to be painful. By mastering these formula techniques, you can transform what used to be hours of tedious visual scanning into a split-second automated process. For modern environments, relying on XLOOKUP or FILTER provides the most robust and elegant solutions. If you are stuck maintaining older legacy spreadsheets, the dependable ISNUMBER + MATCH combo will never let you down. Implement these formulas today to keep your supply chain running smoothly and your stock counts perfectly reconciled.