I have been going through Mike Girvin’s recent book Microsoft 365 Excel: The Only App That Matters, and found an elegant technique of comparing lists using FILTER and XMATCH. It inspired me to create a custom function to show missing or matching entries.

The image below shows two simple lists.

We want to find the entries in List 1 that are NOT in List 2.

The formula in cell E2 is.

=FILTER(A2:A6,ISNA(XMATCH(A2:A6,C2:C6)),"none")

This formulas spills down to display the missing entries.

The magic here is created by the combination of the ISNA and XMATCH functions.

The second argument in the FILTER function is the criteria to determine the rows to display.

The combination of ISNA and XMATCH returns TRUE for missing entries and FALSE for found entries.

The XMATCH function is checking each cell in C2:C6 against the list of entries in B2:B6. If the entry is found a number is returned. Note the XMATCH function defaults to using an exact match. If the entry is not found an #N/A error is returned. We can see the results in the image below.

The ISNA function turns all those #N/A errors into TRUE, and all the numbers into FALSE – see image below.

Those TRUE and FALSE results are used as the criteria to filter the rows. Very clever! Hence missing entries return TRUE and found entries return FALSE.

Showing matching entries

Another cool thing that Mike Girvin shared is that using the ISNUMBER function instead of ISNA, will reverse those TRUE and FALSE entries and change the filter to show the matching entries – see image below.

The formula in cell E2 is.

=FILTER(A2:A6,ISNUMBER(XMATCH(A2:A6,C2:C6)),"none")

Custom Function

A new technique is now available that makes is easy to switch between functions. We can create a single custom function to show either missing or matching entries.

The image below shows the testing LAMBDA function in cell F2.

The formula in cell F2 is.

=LAMBDA(list_1,list_2,[typ],
LET(fn,IF(ISOMITTED(typ),ISNA,IF(typ="Missing",ISNA,ISNUMBER)),
(FILTER(list_1,fn(XMATCH(list_1,list_2)),"none"))))(A2:A6,C2:C6,F1)

The fn variable in the LET function becomes either the ISNA function or the ISNUMBER function. Then we use the fn variable in place of the function name in a formula. This is new functionality in the latest version of Excel.

If the third argument typ is omitted, then the calculation defaults to using ISNA or showing missing entries.

If the text “Missing” or “missing” is used as the third argument then ISNA is also used.

If any other entry is used as the third argument, then ISNUMBER is used which displays matching results.

The custom function definition is shown below.

The custom function in operation is shown in the images below.

Omitting the third argument creates the missing list.

This new functionality of capturing and using a function opens up many opportunities in Excel.