Custom Lambda Function to Reconcile Two Lists

Say you have two lists - one from Aaron, and another from Zora. You want a formula that tells you which items are in which lists so that you can follow up with both parties to get them back on the same page. 

At least, as of right now, Excel doesn't have an easy way to do this, so I wrote a custom Lambda function to do it myself. Here's a simplified example in action: ​

The function produces an array (from a #SPILL formula) that includes a couple of useful elements:

• headers for clarity
• a "Distinct" column that shows all of the unique (non-duplicated) values 
• two columns with Boolean values (1 or 0) indicating whether or not the unique item is in the given list
• a "Category" column that codes the given item into one of 3 categories. Generically: 
  • 1. List 1 Only
  • 2. List 2 Only
  • 3. Both Lists

This way, you can easily target the discrepancies between the two lists (the Category 1s and 2s) or the commonalities (Category 3). 

If you just want to use this Lambda, here's the code you can copy and paste into the "Refers To" field in your name manager: 

Lambda To Paste Into "Refers To" Field of Name Manager

Description to Paste Into "Comment" Field of Name Manager

More Information

Special thanks to Excel MVP Owen Price at Flex Your Data for the inspiration and base method for this formula from his Simple Frequency Table (Owen is an Excel MVP and it's obvious why because his posts are awesome).

I have given more descriptive names to my LET variables (e.g. what Owen calls "X" I call "list1LogicalMatrix"), but his matrix multiplication method is the core feature - otherwise I am simply rearranging the data produced by it. For a great explanation of how this works, I recommend visiting Owen's Simple Frequency Table post. 

Full Lambda Formula (Including Parameters at the End)

Good luck! Happy to hear feedback / comments!