Compare two columns and add missing values in Excel

Here are two lists of names, now I want to compare these two lists and then add the missing names into the List2 as below screenshot shown.

Compare two lists and add missing values with INDEX formula

Compare two lists and adding missing values with a handy comparing tool

Sample file

Here the INDEX formula can compare two lists and then add the missing data at the bottom of the shorter one.

In the cell that below the bottom of the shorter list, type this formula

In the formula, A2:A11 is the longer list, and C2:C7 is the shorter list.

Then press Shift + Ctrl + Enter keys to add the first missing data, then drag auto fill handle down to fill all missing data till #N/A error value appears.

And you can remove the #N/A value after all missing values filled.

If you have Kutools for Excel installed in Excel, you can use the Select Same & Different Cells utility to quickly find the missing data between two lists, then copy and paste them to the shorter list one.

After free installing Kutools for Excel, please do as below:

1. Click Kutools > Select > Select Same & Different Cells. See screenshot:

2. Then in the Select Same & Different Cells dialog, in the Find values in section, choose the longer list, then in the According to section, choose the shorter list, then check Each row and Different Values options.

3. Click Ok > OK, now the missing data in shorter list have been selected, press Ctrl + C to copy them. And then select the cell at the bottom of the shorter list, and press Ctrl + V to paste these missing data.
 

Click to download sample file