How to find missing values in excel using vlookup

In this example, the goal is to generate a list of people who were invited but did not attend an unspecified event.

Missing formula in excel

More specifically, we need to compare the names in B5:B16 against the names in D5:D12 and return the missing names. For convenience, list1 (B5:B16) and list2 (D5:D12) are named ranges. The easiest way to solve this problem in Excel is with the FILTER function and the COUNTIF function as explained below.

FILTER function

This formula uses the FILTER function to retrieve data based on a logical test built with the COUNTIF function.

In the worksheet shown, the formula in cell F5 is:

Working from the inside out, the COUNTIF function is used to create the actual filter:

Notice we are using list2 as the range argument, and list1 as the criteria argument.

In other words, we are asking COUNTIF to count all values in list1 that appear in list2. Because we are giving COUNTIF multiple values for criteria, we get back an array with multiple results:

Note that the array contains 12 counts, one for each value in li