Compare Two Columns for Missing Values in Excel (2024 Update)
In this article, we will explore how to compare two columns in Excel for missing values. Simply follow the steps below.
How to Compare Two Columns in Excel for Missing Data
Below, we outline the steps on how to compare two columns in Excel for missing values using a sample dataset.
1. Insert a New Column to Track Students Who Have Not Submitted Proposals
In our example, we have a list of students registered for a science fair from cells A2 to A9, paired with the names of students who have submitted their project proposals in cells B2 to B9.
Your goal is to compare these two lists to identify students who have not yet submitted their proposals.
For our new column, let’s select column C. Label this new column “Missing Proposals” to help you easily identify which students have yet to submit their project proposals.
2. Enter the Formula to Identify Missing Proposals
Click in the first cell of your new “Missing Proposals” column (cell C2). Enter the formula `=ISNA(VLOOKUP(A2, B:B, 1, FALSE))` to check if the student’s name in cell A2 (from the “Students Registered” list) appears in the “Students Submitted Proposals” column.
This formula returns ‘TRUE’ if the name is not found (indicating a missing proposal), and ‘FALSE’ if the name is found.
Formula Breakdown:
ISNA() checks if the result of the VLOOKUP function is #N/A (not available).
VLOOKUP(A2, B:B, 1, FALSE) looks up the value in cell A2 in column B.
A2 is the value to look up (the student’s name from the “Students Registered” list).
B:B specifies the range to search in (the “Students Submitted Proposals” column).
1 means to return the value from the first column of the specified range.
FALSE specifies an exact match.
3. Apply the Formula to the Entire Column to Compare All Entries
After entering the formula, drag the fill handle (the small square at the bottom-right corner of the cell) down through the column. This action copies the formula to other cells in the column, applying the check to each student listed in the “Students Registered” column.
4. Analyze the Results to Identify Students Who Have Not Submitted Proposals
Review the cells in the “Missing Proposals” column. A result of ‘TRUE’ means the student has not yet submitted their proposal; ‘FALSE’ means they have.
We hope that you now have a better understanding of how to compare two columns for missing values in Excel. If you enjoyed this article, you might also like our article on how to compare two columns in Excel for matches and differences or our article on how to compare two columns in Excel and find matches using VLOOKUP.
