**How to compare the contents of 2 columns with Excel?**

**Write a VLOOKUP functions**The VLOOKUP function will help you to find the missing values

**Use the ISNA function**This function will help to convert the previous result as a test

**Write an IF function**This function will be useful to customize the final result

## Logic to Compare 2 columns in Excel

Comparing 2 columns in Excel is very easy. To solve this problem, we will use the VLOOKUP function.

The trick in this situation is to test if the VLOOKUP functions will returns N/A or not?

- If the value is missing, the VLOOKUP function will return N/A.
- Then, we will perform a test on the result N/A

## Step 1: Write a VLOOKUP function

Let's start by writing a VLOOKUP function to find, or not, an item in the second column.

=VLOOKUP(A1,$D$1:$D$4,1,0)

- First, we want to find the value in A1 is in the second column
- Then, we will verify if this value is present in the column
**$D$1:$D$4**(do not forget the $ tolock the cell references) **The third parameter = 1**because we have selected only one column in the second argument.- And
**last parameter = 0**to perform an exact match.

- Then copy the formula down the column.

The missing value is immediately visible **by the #N/A error value**. NA means Not Applicable.

But it is **not very visually appealing to display #N/A** in your workbook ðŸ™„ðŸ¤¨

## Step 2: Use the ISNA function to perform a test

The ISNA function returns TRUE or FALSE

- TRUE: when the function VLOOKUP returns #N/A
- FALSE: when the function has matched

So, with this function, **instead of returning N/A, we will return TRUE** when the lookup failed.

=ISNA(VLOOKUP(A1,$D$1:$D$4,1,0))

## Step 3: Finish with the IF function

Displaying TRUE or FALSE in the cells is not user-friendly. This is why we will integrate this test into anIF functionto customize the result. We can write the following IF function:

=IF(ISNA(VLOOKUP(A1,$D$1:$D$4,1,0)),"Missing","")

## Change the color of the missing values

If you prefer to change the color, you can insert the logical test with ISNA into a custom rule in conditional formatting. Like that, all the missing values will be displayed with a custom color.

## Tutorial video

In this video, you will turn up all the steps to compare two columns with Excel