Excel Filtering a List Based on Another List

---

2020-01-28

I've been doing some data migration recently for a customer and I've run into a few situations where I have a big list of Customer ID numbers (plus the other associated fields like Name, Address, Email, Phone, etc) and I have a smaller list of Customer ID numbers that I'm interested in. Using Advanced Filtering in Excel, I can get to just the records I'm interested in.

One tip I'll provide up front: put your list of items to filter by on a separate group of rows from your data. The reason is that when you apply the filter, the data rows that don't match will be hidden and when that happens it will also hide some of your criteria rows. You'll see what I mean in a moment...

The easiest way do to this is with an example. Lets say we have a table of customer information such as:

Next, below our table, we'll add a list of ID numbers we'd like to filter by, and then we'll also add a reference cell for our Advanced Filter to use (I called it "Is Filtered"):

The list starting at cell A13 is the list of IDs that you'd like to filter the table with. The cell in C13 is a label for our criteria, and in cell C14 we'll put in the following formula:

=COUNTIF($A$13:$A$16,A2)

COUNTIF takes two arguments, a range and a criteria. In our case, the range is cells A13 to A16 (we put the '$' character before each row/col to tell Excel to keep those as static values - ie: it won't adjust them if used in a formula). The criteria in this case is just the first cell of actual data in our table - in this case A2.

The final move is to select a cell in your data, and then on the Ribbon Toolbar under Data, choose  the Advanced filter button:

In the dialog that comes up, ensure that:

• "Filter the list, in-place" is selected
• "List range" contains the table of data you are looking to filter
• "Criteria range" contains a reference to the filter criteria

In our example, the List Range will already be populated, and you just need to type in:

$C$13:$C$14

into the Criteria Range box:

Once you click OK, your data will be filtered!

I hope that helps someone out!