Skip to main content

Compare two columns and add missing values in Excel

Author: Sun Last Modified: 2019-12-04

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.
doc compare and add missing 1

Compare two lists and add missing values with INDEX formula

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

Sample file

Compare two lists and add missing values with INDEX formula

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.
doc compare and add missing 2

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

Find Missing Sequence Number(s) in Excel List

If there are some missing sequence numbers in a list in Excel, you may need to find the missing sequences one by one manually. But if you have Kutools for Excel's Find Missing Sequence Number utility, it can fill the missing sequence number at once and mark the missing rows as you need.  Click for 30 days free trial!
doc find missing sequence number
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

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

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.

Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. 

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

1. Click Kutools > Select > Select Same & Different Cells. See screenshot:
doc compare and add missing 3

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.
doc compare and add missing 4

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.
doc compare and add missing 5  doc compare and add missing 6

Sample file

Click to download sample file

Other Operations (Articles)

Compare adjacent cells with Conditional Formatting icon sets in Excel
When you compare two columns of data, you want to use the conditional formatting icon sets giving a visual representation of the comparison. For example, if the data in column B is greater than column A, an up arrow icon would show; if column B is less than column A, a down arrow icon would appear; or if column B and A are equal, a right arrow icon would be displayed.

Compare dates if greater than another date in Excel
Supposing you have a list of dates, and want to compare these dates with a specified date in order to find out the date which is greater than that specified date in the list, what would you do? In this article, we will show you methods of comparing dates if greater than another date in Excel.

Compare Excel sheets side by side
If there are two or more sheets across workbooks needed to compare with each other, it may be annoying for you to switch them one by one for comparing. Here I introduce the tricks on comparing sheets side by side horizontally or vertically.

Compare if multiple cells are equal in Excel
As we all know, to compare if two cells are equal, we can use the formula A1=B1. But, if you want to check if multiple cells have the same value, this formula will not work. Today, I will talk about some formulas to compare if multiple cells are equal in Excel.

  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
thank you so much for this. I was into this issue and tired to tinker around to check if its possible but couldnt get it done. Now that i have seen the way you did it, thanks guys
with regards
There are no comments posted here yet
Leave your comments
Posting as Guest
Rate this post:
0   Characters
Suggested Locations