How to find unmatched items with Python in Excel

Many business and data analysis questions ultimately come down to one basic comparison:

Which values appear in one table but not another?

In accounting, that might mean finding invoice numbers that appear in an accounts payable report but not in a payment file. It could also mean identifying payments with no corresponding invoice, purchase orders that never reached the general ledger, customers missing from a master list, or transactions that appear in one system but not another.

This is one of those tasks that Excel can absolutely handle, but the best approach depends on how complete a comparison you need.

Finding unmatched items with lookup formulas

A lookup formula such as XLOOKUP() can tell you whether a value from one table appears in another. The problem is that it only gives you one side of the story at a time.

You can start with the invoice table and look for each invoice in the payment table. That will identify invoices with no payment. But it will not identify payments with no corresponding invoice, because those records do not exist in the table where you wrote the formula.

To get the other side of the comparison, you need to reverse the process and add another lookup to the payment table.

That may be perfectly reasonable for a quick check, but it means maintaining logic in two places. Once you start adding duplicate checks, amount comparisons, status labels, and exception summaries, the workbook can become more complicated than the original question might suggest.

Finding unmatched items with Power Query

Power Query is another very good option. You can load both tables into Power Query, merge them, and use anti-joins to identify records that appear in one table but not the other.

For a repeatable import and data preparation process, that may be exactly the right approach.

The tradeoff is that Power Query operates in a separate query and refresh layer. You build the transformation outside the regular worksheet calculation flow, load the result back into Excel, and refresh it when the source data changes. It is excellent for ETL work, but it is not quite the same as writing an analysis directly in the workbook and seeing the result alongside your other calculations.

Finding unmatched items with Python in Excel

Python in Excel adds another tool to the toolkit.

Using pandas, you can compare both tables directly from a Python cell, return the unmatched records to the workbook, and continue the analysis from there. It does not make formulas or Power Query obsolete. It simply gives you another concise and flexible way to express the comparison.

Let us walk through an accounting example.To follow along, download the exercise workbook below.

The workbook contains two tables: one lists the invoices recorded in the accounting system, while the other lists the invoice numbers included in a payment export. Most invoices appear in both tables, but a handful appear in only one. Our goal is to find those unmatched records.

Importing the data

Start by creating a Python cell in Excel and importing both tables as pandas DataFrames:

invoices_df = xl("invoices[#All]", headers=True)
payments_df = xl("payments[#All]", headers=True)

At this point, the worksheet data is available to pandas without exporting a CSV file or moving the analysis into a separate Python environment.

Find invoices that do not have a payment

Suppose we first want to answer this question:

Which invoices appear in the invoice table but not in the payment table?

Use the following code:

unpaid_invoices = invoices_df[
    ~invoices_df["Invoice"].isin(payments_df["Invoice"])
]

unpaid_invoices

Two invoices, INV-1003 and INV-1005 are the result:

The key part is the isin() method:

invoices_df["Invoice"].isin(payments_df["Invoice"])

This checks every invoice number in invoices_df and returns True when that number also appears in the Invoice column of payments_df.

The ~ reverses the result. In other words, it changes the question from: “Does this invoice appear in the payment table?” to “Does this invoice not appear in the payment table?” The surrounding brackets then filter invoices_df to keep only those unmatched invoices.

This is similar to using a lookup formula and filtering for #N/A, but the pandas code states the intent directly: return invoices whose invoice numbers are not in the payment table.

You could also total the value of those invoices:

unpaid_invoices["Amount"].sum()

That gives the accounting team both the individual exceptions and the total amount requiring investigation.

Find unmatched records in both directions

The first example still tells us only one side of the story. It finds invoices without payments, but it does not find payments whose invoice numbers are missing from the invoice table.

For a full reconciliation, use an outer merge:

comparison = invoices_df.merge(
    payments_df,
    on="Invoice",
    how="outer",
    indicator=True,
    suffixes=("_invoice", "_payment")
)

comparison

This combines the two DataFrames using the Invoice column as the matching field. We now have a full accounting of which invoice is found in which table.

The important argument is how="outer". An outer merge keeps every invoice number from both tables, whether or not it has a match. The indicator=True argument adds a column named _merge that tells us where each record came from:

• both means the invoice number appears in both tables
• left_only means it appears only in invoices_df
• right_only means it appears only in payments_df

We can now filter out the records that matched:

unmatched = comparison.query('_merge != "both"')

unmatched

The result contains every invoice number that appears in only one of the two tables:

This gives us both sides of the reconciliation in one result:

• invoices recorded in the accounting system with no matching payment
• payments containing an invoice number that does not appear in the accounting system

Why this is a useful Python in Excel pattern

A formula-based approach is still perfectly valid. For example, you could place a lookup formula in the invoice table and check whether each invoice appears in the payment table. But to perform a true two-way reconciliation, you would also need to place another formula in the payment table and check in the opposite direction.

The logic is divided between two tables because a lookup begins with the records in one table and searches for them in another. It cannot return records that exist only in the lookup table because those records were never part of the starting list.

The pandas outer merge treats the reconciliation as one complete operation:

• compare these two tables,
• match them using invoice number,
• retain all records from both sides, and
• identify where each result originated.

Power Query can perform much the same operation and remains an excellent choice for scheduled or repeatable data preparation. Python in Excel is attractive when you want the comparison to remain part of the workbook analysis itself, when you prefer code to a sequence of query interface steps, or when reconciliation is only the beginning of the analysis.

From here, you could extend the pandas workflow to:

• flag duplicate invoice numbers,
• compare invoice and payment amounts,
• allow for small rounding differences,
• summarize unmatched amounts by vendor,
• identify partial payments, or
• create an exception report for review.

That is the broader value of Python in Excel. It does not replace the tools already available in Excel. It expands the range of ways you can solve the problem.

Learn more Python in Excel

This is the type of practical finance and accounting workflow covered in my forthcoming book, Python in Excel for Data Analytics.

The book shows how to combine the familiarity of Excel with Python tools such as pandas, NumPy, Matplotlib, and statistical modeling libraries for real-world business analysis.

Learn more about the book here: