Search code examples
filtergoogle-sheetsgoogle-sheets-formula

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

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?

Solution
    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)