Compare Two Lists in Excel: XLOOKUP, FILTER, and COUNTIF

Comparing two lists in Excel is one of the most common tasks in accounting, finance, and operations. Whether we need to confirm that every open invoice has been paid, find bank deposits missing from the books, or run a full two-way reconciliation, the right formulas turn a manual, error-prone process into a clean, dynamic exception report. In this tutorial, we walk through three exercises that show how to use XLOOKUP, FILTER, and COUNTIF together to answer the questions: is it in both lists, and if so, is the amount correct?

Exercise 1: Matching Invoices and Checking Amounts with XLOOKUP

Let’s say we have two data sets on the same worksheet. List A is our open accounts payable register showing invoices that are still showing as open. List B is a payment register containing every invoice that has actually cleared.

The purpose of this exercise is to answer two questions at once: (a) has the invoice been paid, and (b) was it paid for the expected amount?

In case we are new to the XLOOKUP function, it searches a lookup array for a value and returns a corresponding result from a return array. The function arguments are:

• lookup_value: the value to search for
• lookup_array: the range or array to search in
• return_array: the range or array to return a result from
• if_not_found: value returned when no match is found (optional)
• match_mode: specifies how to match: 0 for exact match (optional)
• search_mode: specifies the search direction (optional)

We want to look up each invoice number from our open AP list inside the payment register’s Invoice column. If a match exists, we want to return the Paid Amount. We could write the following formula into cell F9:

=XLOOKUP(B9,Table1[Invoice],Table1[Paid Amount])

We fill the formula down, and bam: where a match exists, we see the paid amount. Where no match exists, we get an #N/A error, which tells us that invoice has not been paid yet.

The #N/A errors are actually useful information, but we can make it friendlier. We just modify the formula to include the optional if_not_found argument. So, we use the following formula:

=XLOOKUP(B9,Table1[Invoice],Table1[Paid Amount],"Unpaid")

Now that we have the paid amounts pulling through cleanly, we can add a Diff column to subtract the paid amount from the invoiced amount. We point to cell G9 and write:

=E9-F9

When we fill that down, we notice that rows with “Unpaid” in the Paid Amount column produce an error in the Diff column, because we cannot subtract text from a number. No worries, we can wrap that formula in IFERROR to return an empty string instead.

Since we are about to use IFERROR, here is what it does. It returns the formula’s result when no error occurs, and returns a value we specify when the formula does produce an error. The function arguments are:

• value: the formula or expression to evaluate
• value_if_error: the value to return if the formula produces an error

=IFERROR(E9-F9,"")

We fill this updated version down, and bam: a clean difference column. Any invoice paid for a different amount pops right out. Notice that INV-10440 was for 975 but was paid for 900, so the Diff column shows 75 and flags it for further investigation.

Exercise 2: Dynamic Missing-Item Report with FILTER and COUNTIF

But what if we wanted to get a dynamic list of everything in List A that is missing from List B? Well, let’s tackle that in the next exercise. Here we have bank deposits (List A) and an AR cash receipts log (List B).

The goal is an exception report that refreshes automatically whenever the underlying data changes. No helper columns, no rewriting formulas. We combine COUNTIF and FILTER to make it happen.

Before diving in, a quick look at the COUNTIF function. It returns the count of cells in a range that meet a single condition. The function arguments are:

• range: the range of cells to count within
• criteria: the condition that determines which cells to count

Here is a quick refresher on the FILTER function. It returns a subset of a range based on a condition we define. The function arguments are:

• array: the range to return rows from
• include: a boolean array the same height as array that tells FILTER which rows to keep
• if_empty: value returned when no rows match (optional)

The logic works like this: COUNTIF checks how many times each bank deposit reference appears in the AR table’s Ref column. Where that count is zero, the deposit is missing from the books. FILTER then returns only those rows. So, we use the following formula:

=FILTER(tbl_Bank,COUNTIF(tbl_AR[Ref],tbl_Bank[Ref])=0)

Hit Enter, and bam: the spill range fills with only the rows that are missing from the books. DEP-304, DEP-306, DEP-308, and DEP-310 are all in the bank export but do not appear in the AR cash receipts log.

Because this is a dynamic spill formula, we do not need to touch it again next period. Just paste in new transactions and the exception report updates on its own. In practice, we would place each table on its own worksheet and put the exception report on a separate sheet as well, to avoid spill errors if the lists grow.

Exercise 3: Two-Way Reconciliation with FILTER and COUNTIF

Now it is time to make things more interesting. Exercise 3 takes the same FILTER and COUNTIF combo and runs it in BOTH directions. The purpose is a two-way reconciliation: items in A not in B, AND items in B not in A.

Consider the following worksheet: we have GL cash activity on one side and bank statement activity on the other, and we want a single exception report with two columns side by side.

In GL Not in Bank

First, we find everything recorded in the GL that has not yet appeared on the bank statement. We write the following formula into cell B9:

=FILTER(GL[Tran ID],COUNTIF(bank[Tran ID],GL[Tran ID])=0)

In Bank Not in GL

Next, we flip the logic to find everything on the bank statement that does not appear in the GL. We write the following formula into cell C9:

=FILTER(bank[Tran ID],COUNTIF(GL[Tran ID],bank[Tran ID])=0)

We hit Enter, and bam: the complete two-way exception report. T-1007 and T-1012 are in the GL but missing from the bank. T-1013 and T-1014 are on the bank statement but missing from the GL. Both columns spill dynamically AND update automatically when the source tables are refreshed.

Because these formulas are fully dynamic, next period all we do is paste in new transactions. The exception reports update automatically. No rewriting, no manual checking. Mission accomplished!

Summary

In this tutorial, we covered three practical exercises for comparing two lists in Excel. In Exercise 1, we used XLOOKUP to pull paid amounts from a payment register into our open invoice list, spot unpaid invoices, and flag amount discrepancies with a Diff column wrapped in IFERROR. In Exercise 2, we combined FILTER and COUNTIF to produce a dynamic, no-helper-column exception report of items in List A that are missing from List B. In Exercise 3, we extended that same pattern to build a two-way reconciliation that catches missing items in BOTH directions from a single, automatically refreshing report.

If you have any suggestions, improvements, alternatives, or questions, please share by posting a comment below … thanks!

Sample File

FAQs

Which Excel version do I need to use FILTER and XLOOKUP?

Both FILTER and XLOOKUP require Excel 365 or Excel 2021. They are not available in Excel 2019 or earlier. If we are on an older version, VLOOKUP paired with IFERROR can handle the XLOOKUP scenarios, though we would need a helper column approach to replicate the FILTER logic.

What does COUNTIF return when it checks for matches across two lists?

When we pass a column reference as the criteria argument, COUNTIF evaluates each cell in the lookup range against each value in the criteria column and returns an array of counts. A count of zero means no match was found, which is exactly the condition we use inside FILTER to isolate the missing rows.

Why does the Diff column show an error when the Paid Amount says ‘Unpaid’?

Excel cannot subtract a text string like “Unpaid” from a number. Wrapping the subtraction formula in IFERROR and returning an empty string keeps the column clean and readable. Alternatively, we could use a zero as the if_not_found value in XLOOKUP so the math always works, but that would obscure which invoices are genuinely unpaid.

Can we return more than just the Tran ID in the Exercise 3 exception report?

Absolutely. Instead of referencing only the Tran ID column in the FILTER array argument, we point to the entire table, for example GL instead of GL[Tran ID]. The formula will then spill all columns from the matching rows. We just need to make sure there is enough empty space to the right for the spill range.

What happens if a reference number appears more than once in List B?

COUNTIF returns the total count of matches, so a reference appearing twice would return 2. Since we filter for counts equal to zero, duplicates would NOT appear in the missing-items report. If we need to catch duplicates specifically, we would change the condition to something like COUNTIF(…)>1.

Why is it recommended to put each table on its own worksheet?

A spill formula needs contiguous empty cells to expand into. If the source tables are on the same sheet and grow downward, the spill range can collide with the table data and produce a spill error. Keeping each table on its own worksheet and the exception report on a separate sheet eliminates that risk entirely.

Can XLOOKUP handle lookups where the invoice numbers are formatted differently in each list?

XLOOKUP does an exact match by default, so formatting inconsistencies like leading spaces or mixed case will cause missed matches. We can use TRIM and UPPER inside the formula to normalize values before comparing, for example wrapping both the lookup_value and the lookup_array references in TRIM to remove stray spaces.

Is there a way to highlight mismatches with Conditional Formatting instead of a Diff column?

Yes. We can apply a Conditional Formatting rule to the Amount column using a formula like =E9<>F9 and choose a highlight color. This gives a visual flag without an extra column. That said, the Diff column approach is often more useful because it shows the exact dollar discrepancy at a glance.

Does this approach work if the two lists have a different number of rows?

Yes, and that is actually the point. XLOOKUP searches the entire lookup array regardless of size, and COUNTIF evaluates every row in the criteria range against every row in the lookup range. The lists do not need to be the same length, sorted, or organized in any particular way.

How do we handle a two-way reconciliation when the field titles differ between the two tables?

No problem. We just reference the correct column from each table explicitly in the formula. For example, if List A uses a column called [Deposit Ref] and List B uses [Reference Number], we write COUNTIF(tbl_B[Reference Number], tbl_A[Deposit Ref])=0. The column names do not need to match as long as the values within them are consistent.