Compare two lists in Excel

The Compare two lists guide

=IF(COUNTIF($E$2:$E$8, A2)>0, "Paid", "Missing") checks whether A2 appears anywhere in the other list. Comparing A2=E2 only works if both lists are in the same order, and they almost never are.

CoreAbout 3 minutesBy Eshagh ShahnavaziUpdated 8 Nov 2026

The job

You have two lists that should match: invoices sent and payments received, an order list and a delivery list, last month's customers and this month's. You need to know which items in one are missing from the other.

Count it in the other list

=IF(COUNTIF($E$2:$E$8, A2)>0, "Paid", "Missing")

COUNTIF counts how many times A2 appears in the bank list. 0 means it isn't there. The IF turns the count into a word. Lock the list with $, or it slides down as you fill.

Or find its position

=IF(ISNUMBER(MATCH(A2, $E$2:$E$8, 0)), "Paid", "Missing")

MATCH returns a position when it finds the value and #N/A when it doesn't. ISNUMBER turns that into TRUE or FALSE. In Excel 365, XLOOKUP with an if_not_found argument does a similar job.

Why A2=E2 doesn't work

It compares each invoice with the payment on the same row. Payments arrive in their own order, so a paid invoice shows "Missing" because its payment is three rows down. Search the whole list instead.

Before you trust the result

• Extra spaces. "INV-1003 " doesn't match "INV-1003". TRIM fixes it; see the #N/A mistake hunt.
• Numbers vs text. An ID stored as text doesn't match the same ID stored as a number.
• Both directions. This finds invoices with no payment. Swap the lists to find payments with no invoice, like INV-0998 in the challenge.

Common mistakes

• Comparing row by row. Works only when both lists are in the same order.
• The list not locked. It slides as you fill, and the payments at the top drop out.
• COUNTIF arguments swapped. The list comes first, the value second.
• The labels the wrong way round. A count above 0 means found.

FAQ

How do I compare two lists in Excel?

Check each item against the whole other list: =IF(COUNTIF($E$2:$E$8,A2)>0,"Found","Missing"), filled down. Lock the list with $.

How do I find items in one list that are not in another?

Use COUNTIF: a count of 0 means the item isn't in the other list. =COUNTIF($E$2:$E$8,A2)=0 returns TRUE for the missing ones.

Why does comparing A2=E2 not work?

It only compares items on the same row. If the lists are in a different order, matching items sit on different rows and look missing.

Can I use VLOOKUP to compare two lists?

Yes: a VLOOKUP or MATCH that returns #N/A means the item isn't there. Wrap it so the error becomes a word, for example =IF(ISNA(MATCH(A2,$E$2:$E$8,0)),"Missing","Found").

This task is in the timed data analyst practice test. What Excel interview tests check