# Difference Between two lists

Copper Contributor

# Difference Between two lists

How to find difference between two given lists?

We generally use VLookup and other lookup functions for finding the common entries from 2 lists. Here, I have given formula for finding the differences in the lists. in A column, we have recipients of the meeting invite and in B column we have the attendees list. We want to find out who were all not attended the meeting. Here I have used COUNTIF function for finding the attendees, keeping attendees list in the range and recipient list in the criteria. It returns the count as 0s and 1s; 0's for the not attended and 1's for the attendees. After that, using FILTER function, and checking if the COUNTIF function results is zero. Filter function return the not attended list.

2 Replies

# Re: Difference Between two lists

Maybe you can use the LET function, which allows you to create a variable and use it within a formula.

=LET(not_attended, FILTER(A:A, COUNTIF(B:B, A:A) = 0), not_attended)

LET function is not tested, use at the moment Excel 2016.

Otherwise, if it has to be a formula, the example would have to be a Countif function in column C and then the filter function in column D. Something like that.

Column C: Formula: =COUNTIF(B:B, A2)

Column D: Formula: =FILTER(A:A, C:C = 0).

My answers are voluntary and without guarantee!