How to compare two columns and find missing values in Excel?
In Excel, you’ll often work with two related columns of data where you need to check whether all values in one column exist in the other. For example, as shown in the screenshot below, column 1 may contain a complete list of items, while column 2 has only part of that list. In such cases, identifying which values are missing helps ensure data consistency and accuracy. This article demonstrates two ways to compare two columns and find missing values in Excel - using formulas and VBA - so you can quickly choose the method that best suits your workflow.
- Compare two columns and find missing values with a formula
- Compare two columns and find missing values with VBA
Compare two columns and find missing values with a formula
In Excel, you can easily check whether values in one column also appear in another using a simple formula. This approach helps you identify missing or unmatched entries, making it ideal for validating data, cleaning up lists, or preparing tables before merging.
1. Select a blank cell (for example, E2) where you want to return the comparison result. Enter the following formula and press Enter:
=IF(COUNTIF(B:B,A2)=0,"Missing",A2)
Notes:
2. After entering the formula, drag the Fill Handle down to apply it to other rows. The new column will show which values from column A exist in column B, and display “Missing” for those that do not.
Pros & Cons:
Advantages: Simple and fast to set up; clearly identifies missing or unmatched data.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Compare two columns and find missing values with VBA
If you frequently need to check for missing values between two columns, a VBA macro can automate the process and handle larger datasets efficiently. The VBA approach allows you to loop through each cell and mark entries that are not found in the comparison column, saving time and reducing manual errors.
Precautions & Tips: Always save your workbook before running VBA, as macros can make irreversible changes. Test on sample data to avoid errors such as accidental overwriting of your source columns.
1. Go to the Developer tab and click Visual Basic to open the Microsoft Visual Basic for Applications editor. If the Developer tab isn’t visible, see this guide: Show the Developer tab in Excel.
2. In the VBA editor, click Insert > Module to create a new module. Then, paste the following code into the editor window:
Sub CompareColumnsFindMissing()
Dim ws As Worksheet
Dim rngA As Range, rngB As Range, c As Range
Set ws = ActiveSheet
Set rngA = ws.Range("A2:A100") 'Column to check
Set rngB = ws.Range("B2:B100") 'Reference column
For Each c In rngA
If Application.WorksheetFunction.CountIf(rngB, c.Value) = 0 Then
c.Offset(0, 2).Value = "Missing"
Else
c.Offset(0, 2).Value = c.Value
End If
Next c
End SubTips: You can modify the range references ("A2:A100" and "B2:B100") to suit your dataset size.
3. Press F5 or click the Run button 
to execute the macro. The code will generate results in column C, listing matching values or displaying “Missing” for those not found in column B.
Pros & Cons:
Advantages: Automates comparison for large datasets; delivers consistent and accurate results.
Disadvantages: Requires enabling macros and some familiarity with VBA; not ideal for quick, one-time checks.
Related articles:
- How to count cells match to either X or Y in Excel?
- How to find matched value by searching upwards in Excel?
- How to match dates by month and year only in Excel?
- How to check if a cell value match to a list in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- 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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in