How to Compare Two Lists in Excel

Comparing two lists in Excel to find unique or missing items is a common task. This article outlines how to achieve this efficiently using formulas available in Excel 2013, providing a solution for identifying entries present in one list but absent in another.

Identifying Differences Between Two Excel Lists

While conditional formatting can highlight differences, using a formula to generate a separate list of unique items offers a more direct solution. Let’s explore how to accomplish this in Excel 2013, achieving a similar outcome to the ARRAYFORMULA function found in Google Sheets.

Recreating the ARRAYFORMULA Functionality in Excel 2013

The Google Sheets formula =ARRAYFORMULA(FILTER('Building Static'!D3:F522,ISERROR(MATCH('Building Static'!D3:D522,B21:B91,0)))) effectively filters a list to display only unique entries. To replicate this in Excel 2013, we can leverage the IF, ISERROR, MATCH, and INDEX functions.

Using Excel’s Formula for Comparing Lists

Assuming “List 1” (Master List) is on the ‘Building Static’ sheet in cells D3:D522 and “List 2” is on the current sheet in cells B21:B91, the following formula in Excel 2013 can generate “List 3” (unique items):

=IFERROR(INDEX('Building Static'!$D$3:$D$522,SMALL(IF(ISERROR(MATCH('Building Static'!$D$3:$D$522,$B$21:$B$91,0)),ROW('Building Static'!$D$3:$D$522)-ROW('Building Static'!$D$3)+1),ROW(1:1))),"")

This is an array formula, so you need to press Ctrl + Shift + Enter after entering it to make it work correctly. Then drag it down to accommodate the potential number of unique items.

Formula Breakdown

• MATCH: Checks if each item in ‘List 1’ exists in ‘List 2’. If found, it returns the position; otherwise, it returns an error.
• ISERROR: Checks if the MATCH function returned an error, indicating the item is not in ‘List 2’.
• IF: If ISERROR is true, the IF function returns the row number of the unique item in ‘List 1’.
• SMALL: Returns the nth smallest value from the array of row numbers generated by the IF function. ROW(1:1) acts as a counter, incrementing as you drag the formula down.
• INDEX: Retrieves the actual item from ‘List 1’ based on the row number provided by SMALL.
• IFERROR: Handles potential errors, displaying a blank cell if no more unique items are found.

Conclusion

Comparing two lists in Excel 2013 to identify unique items is achievable using the formula outlined above. While Excel 2013 lacks the ARRAYFORMULA function of Google Sheets, leveraging the combined power of IF, ISERROR, MATCH, INDEX, and SMALL provides a robust solution for this common task. Remember to enter the formula as an array formula using Ctrl + Shift + Enter.