- HD
- 720p
- 540p
- 360p

- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x

We hope you enjoyed this lesson.

Cool lesson, huh? Share it with your friends

## About this lesson

Explore one of the newer functions in Excel, FILTER, and learn how to use it with COUNTIF to compare two lists.

## Exercise files

Download this lesson’s related exercise files.

Compare Lists with FILTER, COUNTIF and NOT17.5 KB Compare Lists with FILTER, COUNTIF and NOT - Completed

19.9 KB Compare Lists with FILTER, COUNTIF and NOT

43.2 KB Compare Lists with FILTER, COUNTIF and NOT

10.9 KB Compare Lists with FILTER, COUNTIF and NOT - Solution

70.5 KB Compare Lists with FILTER, COUNTIF and NOT - Solution

12.6 KB

## Quick reference

### Compare Lists with FILTER, COUNTIF, and NOT

Learn how to compare lists using Excel formulas.

### When to use

We use Excel formulas to compare lists of data whenever we want our comparison results to output to a different range of cells as opposed to highlighting in place. We can also extend the power of our comparison by combining multiple formulas together.

### Instructions

In this example, we have a list of Invitees and a list of Attendees. We need to find out who was invited to the event but did not attend so we can send a follow-up email.

We also want to make this dynamic so that if anything changes on either list, the formula updates.

#### Create a Table

To make our lists dynamic, we need to format our data as an Excel table. Excel tables auto-expand when new data is added.

- Click anywhere inside the data.
- Press
**CTRL+T**. - Ensure
**My table has headers**is checked. - Click
**OK**.

#### Use COUNTIF, NOT, and FILTER

We need to use three formulas to compare these lists: COUNTIF, NOT, and FILTER. To understand this a little better, we are going to reverse engineer this.

Let's start by using the COUNTIF formula. COUNTIF will count cells based on a condition.

- Type =COUNTIF

The first argument is **range**. The range is the range of cells we want to search in for our criteria.

- Select the
**Attendees**column in the table. - Press comma.

The next argument is **criteria**. The criteria is what we are searching for in the range.

- Select the
**Invitees**column in the table. - Close the bracket and press
**Enter**.

The formula will return 1's and 0's. It's looking in the **Invitees **list for the name and then looking in the **Attendees **list to see if it can find a match.

A result of 1 means the name occurs in both lists, and a result of 0 means the name only appears in the first list.

We can now turn these 1's and 0's into TRUE and FALSE using the NOT function. NOT is a logical formula that tells if something is not true or not false.

- Click in the formula to edit.
- Add the
**NOT**function.

The 1's are now replaced with FALSE and the 0's are replaced with TRUE.

We can now use the FILTER function to filter the invitees.

The result is a list of all of the no-shows.

The formula is completely dynamic so if we were to delete Tom Bruise from the Attendees list, effectively making him a no-show, everything updates.

### Hints & tips

- Remember, when data is in a table all formulas will use table references as opposed to cell references.

Lesson notes are only available for subscribers.