A zero value indicates a value inlist1thatis notfound inlist2.

Any other positive number indicates a value inlist1thatisfound inlist2.

Any value inlist1associated with a zero is removed, while any value associated with a positive number survives.

FILTER to extract non-matching values with NOT

The result is an array of 7 matching values whichspillinto the range F5:F11.

Non-matching values

To extract non-matching values fromlist1(i.e.

The result is a list of the values inlist1that are not present inlist2.

FILTER to extract non-matching values with NOT

The core of this formula is theINDEX function, which receiveslist1as thearrayargument.

Most of the remaining formula simply calculates the row number to use for matching values.

For another example of this idea, seethis formula.

FILTER to extract non-matching values with NOT

The output from FILTER is dynamic.

If source data or criteria change, FILTER will return a new set of results.

COUNTIF can be used to count cells that…

FILTER to extract non-matching values with NOT

FILTER to extract non-matching values with NOT

Extract matching values with INDEX

Excel formula: Filter text contains

Excel formula: List missing values

Excel formula: Filter this or that

Excel formula: Basic filter example

Excel formula: Get nth match with INDEX / MATCH

Excel formula: Get relative row numbers in range

Excel FILTER function

Excel COUNTIF function

Excel COUNTIFS function

FILTER to extract non-matching values with NOT

Extract matching values with INDEX