How to filter one list of items from another list of items?

filter google-sheets google-sheets-formula
viv227295 picture viv227295 · Mar 31, 2015 · Viewed 9.5k times · Source

I have a huge list of items in Column A (1,000 items) and a smaller list of items in Column B (510 items).

I want to put a formula in Column C to show only the Column A items not in Column B.

How to achieve this through a formula, preferably a FILTER formula?

Answer

Andy picture Andy · Mar 31, 2015
  1. Select the list in column A
  2. Right-Click and select Name a Range...
  3. Enter "ColumnToSearch"
  4. Click cell C1
  5. Enter this formula: =MATCH(B1,ColumnToSearch,0)
  6. Drag the formula down for all items in B

If the formula fails to find a match, it will be marked "#N/A", otherwise it will be a number.

If you'd like it to be TRUE for match and FALSE for no match, use this formula instead:

=ISNUMBER(MATCH(B1,ColumnToSearch,0))

If you'd like to return the unfound value and return empty string for found values

=IF(ISNUMBER(MATCH(B1,ColumnToSearch,0)),"",B1)

Related questions

Filter a range by array

I have a Google Spreadsheet containing the teams of the UEFA EURO 2012, and their scores: Team Points Goals scored ------ ------ ------------ Germany 6 3 Croatia 3 3 Ireland 0 1 ... ... ... Now I want to filter that list, so that the result contains only a subset …

Read More
Google Sheets: How do I filter view two columns OR instead of AND

In Google Sheets, I want to filter view my results so only rows with an "x" in column D AND/OR column E are shown. If I filter to show the columns with an "x" in them, it will only …

Read More
How to sort only those rows which have no blank cell?

I have a Google Spreadsheet with two separate sheets. The first one is just a big list of names and data, and the second is supposed to be a sorted listing of all the data on the first sheet (sorted …

Read More