Table of Contents
In Excel, comparing two columns is a useful tool to identify any missing values between them. To do so, you can use the “IF” function along with the “COUNTIF” function to check if the values in one column are present in the other column. This will generate a result of “TRUE” or “FALSE” for each row, indicating if there is a match or not. Any rows with a result of “FALSE” can be considered as missing values. This simple comparison process can help in efficiently identifying and managing missing data in Excel.
Excel: Compare Two Columns for Missing Values
You can use the following formula in Excel to compare two columns and find missing values:
=FILTER(A2:A13, ISNA(VLOOKUP(A2:A13, B2:B7, 1, FALSE)))
This particular formula finds all of the values in A2:A13 that are missing from the range B2:B13.
The following example shows how to use this formula in practice.
Example: Compare Two Columns for Missing Values in Excel
Suppose we have the following two lists of names in Excel:
Suppose we would like to find all of the names from List A that are missing in List B.
We can type the following formula into cell D2 to do so:
=FILTER(A2:A13, ISNA(VLOOKUP(A2:A13, B2:B7, 1, FALSE)))
The following screenshot shows how to use this formula in practice:
The formula returns every name from List A that is missing from List B.
For example:
- The name “Bob” appears in List A but it does not appear in List B.
- The name Chad” appears in List A but it does not appear in List B.
- The name “Doug” appears in List A but it does not appear in List B.
And so on.
How This Formula Works
=FILTER(A2:A13, ISNA(VLOOKUP(A2:A13, B2:B7, 1, FALSE)))
Here is how this formula works:
First, we use VLOOKUP to attempt to look up each value from A2:A13 in B2:B7.
If the name is found, then the name is returned. Otherwise, #N/A is returned.
Then, we use FILTER along with the ISNA function to filter the names from A2:A13 that had a result of #N/A when we used the VLOOKUP function.
The end result is that we’re able to only see the names from A2:A13 that are missing from B2:B7.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I compare two columns in Excel to identify missing values?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-compare-two-columns-in-excel-to-identify-missing-values/
stats writer. "How can I compare two columns in Excel to identify missing values?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-compare-two-columns-in-excel-to-identify-missing-values/.
stats writer. "How can I compare two columns in Excel to identify missing values?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-compare-two-columns-in-excel-to-identify-missing-values/.
stats writer (2024) 'How can I compare two columns in Excel to identify missing values?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-compare-two-columns-in-excel-to-identify-missing-values/.
[1] stats writer, "How can I compare two columns in Excel to identify missing values?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I compare two columns in Excel to identify missing values?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.