Comparing the content of two columns is a very common task in Excel. In this article, you’ll learn 3 powerful formulas to identify missing values between two columns.

1. Using VLOOKUP and ISNA

The VLOOKUP function is usually used to find a value in a reference table based on an ID. In this case, we will use it to check when a value is missing. If the value is not found, VLOOKUP will return #N/A.

In this example, the VLOOKUP function compares the dates in the blue column with the dates in the purple column. When a date is missing, it shows #N/A.

This result is not ideal because having errors like #N/A in your worksheet looks unprofessional. But also, because the errors is what we want to keep. To fix this, we use the ISNA function to change the error into a message. When the test returns TRUE, we display "Missing."

Explanation:

• VLOOKUP(B3,$E$3:$E$9,1,0) looks for the value in cell B3 inside the range $E$3:$E$9. The 0 ensures an exact match.
• If the value is not found, VLOOKUP returns #N/A.
• ISNA() checks if the result is #N/A and returns TRUE if the value is missing.
• The IF function shows "Missing" if the value is not found. If the value exists, it leaves the cell blank (or you can show a custom message).

---

2. Using COUNTIF to compare two columns

Another way to find missing values is to use the COUNTIF function. The COUNTIF function counts how many times a value appears in a range. For example, in this case, we count how many times a value from Column C is found in Column F. If the function returns 0, it means the value is missing.

Next, we turn this result into a logical test to check if the result equals 0. Then, we use the IF function to display "Missing" when the logical test is TRUE.

Explanation:

• COUNTIF($F$3:$F$9,C3) counts how many times the value in C3 appears in Column F.
• If the count is 0, which means the value is missing in Column F.
• We create a logical test to check if the result equals 0.
• The IF function checks this condition and displays "Missing" for missing values or leaves the cell blank if the value exists.

3. Using FILTER to Group Missing Values

In the previous examples, we created results row by row. While this works, it can become hard to spot missing values when you’re working with hundreds of rows. If you’re using Excel 365, the FILTER function can help group all the missing values in one place. The FILTER function extracts values from a column when a logical test is TRUE.

Here, we reuse the COUNTIF function to test if a value is missing. However, instead of testing one cell at a time, we change the second argument to all the cells in Column C.

Explanation:

• COUNTIF($F$3:$F$9,$C$3:$C$10) counts how many times each value in Column C appears in Column F.
• A logical test checks if the count equals 0 for each value.
• FILTER() extracts only the values from Column C where the count is 0, meaning those values are missing in Column F.

This approach is especially useful for quickly identifying missing values in large datasets 😃👍