How to filter one list of items from another list of items?
12,021
- Select the list in column A
- Right-Click and select Name a Range...
- Enter "ColumnToSearch"
- Click cell C1
- Enter this formula:
=MATCH(B1,ColumnToSearch,0)
- 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)
Author by
viv227295
Updated on June 13, 2022Comments
-
viv227295 almost 2 years
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?