There are a lot of ways to **compare two lists** or columns in Excel. In this article, I am going to compare/find matches in two columns in excel using **VLOOKUP**. In one of my previous articles, I discussed **How to Compare Two Columns or Lists in Excel** where I discussed other methods of comparison.

Before starting the comparison, I will discuss the syntax, arguments, and other necessary things about the **VLOOKUP** function.

**Table of Contents**hide

## Download Practice Workbook

## An Overview of Excel VLOOKUP Function

The V in **VLOOKUP** stands for “vertical”. **VLOOKUP**. The **VLOOKUP** is a built-in function of Excel which performs the vertical lookup by searching a specific value of a column in another column.

- The syntax of the
**VLOOKUP function**is:

**VLOOKUP( lookup_value, table_array, col_index_number, [range_lookup] ). **The arguments which are stated in this syntax have a specific meaning.

**Arguments Explanation:**

Argument | Required/Optional | Explanation |
---|---|---|

Lookup_value |
Required | The value which will be used as the lookup value. |

Table_array |
Required | Range of data where the value will be searched. |

Col_index_number |
Required | The column from the range from which we will get the value. |

Range_lookup |
Optional | TRUE is used for the approximate match and FALSE is used for the exact match. If this argument is omitted Excel uses the TRUE parameter as default. |

The results from the **VLOOKUP** function can be text strings or numeric data depending on the data you are using. If **FALSE** is used as **[range_lookup]** then it will find an exact match. If no exact match is found then it will return the value **#N/A**. If **TRUE** is used as **[range_lookup]** then it will look for an approximate match. When no approximate match is found then it will return the next smaller value.

**Read More:** **Excel LOOKUP vs VLOOKUP: With 3 Examples**

## 2 Ways to Compare Two Columns Using VLOOKUP in Excel

### 1. Using Only VLOOKUP Function for Comparison Between Two Columns

Let`s say you have two columns where some colors are listed. I am going to compare these two columns for exact matches. Like, If I choose the color Blue from the 1^{st} column and lookup for this color in the 3r^{d} column it will start looking up for the blue color and if this color doesn’t exist then it will return the value **#N/A**. So, let`s start the comparison. The color lists are listed below and in the Existence column, the comparison will be shown.

**Steps:**

- In
**Cell C2**write the formula-

`=VLOOKUP(B5,$D$5:$D$10,1,FALSE)`

- Later, press the
**ENTER**button for the output.

- You will see the value red is found in that cell. Now drag down the
**Fill Handle**tool to copy the formula for the rest of the cells in the column. You will see the result that you were looking for.

Here’s the whole comparison.

The **#N/A** results are found because the color Blue and White are not in the Color List-2.

**
Note: We specify the range as $D$5:$D$10. The “$” is used to make the cells absolute and constant in a formula. So, whenever you copy the formula for other cells it will use the same range.**

**Read More:** **Why VLOOKUP Returns #N/A When Match Exists? (5 Causes & Solutions)**

### 2. Using IF, ISNA, and VLOOKUP for Comparison Between Two Columns

Here I will be using the same example. But I will insert two new functions with the **VLOOKUP** function. If there is no exact match between the columns the formula will return NO. If matches exist the formula will return YES with respect to the first column. The color lists are listed below and, in the **Color** **Exists** column, the comparison will be shown.

**Steps:**

- Now write the formula in
**Cell C5-**

`=IF(ISNA(VLOOKUP(B5,$D$5:$D$10,1,0)),"NO","YES")`

- Then hit the
**ENTER**button to finish.

**Formula Breakdown:**

**VLOOKUP(B5,$D$5:$D$10,1,0)**

The **VLOOKUP** function will return the output for the lookup value.

**ISNA(VLOOKUP(B5,$D$5:$D$10,1,0))**

Next, the **ISNA** function will return TRUE if it gets **#N/A** error otherwise will return **FALSE**.

**=IF(ISNA(VLOOKUP(B5,$D$5:$D$10,1,0)),”NO”,”YES”)**

Finally, the **IF** function will return **NO** for **TRUE** and **YES** for **FALSE**.

- The formula will return the value YES as the color Red exists in the two columns. Now drag the
**Fill Handle**tool to the rest of the cell to show the comparison between the two columns.

A few moments later, you will get all the output like the image below.

- Here we are getting the result NO as the colors Blue and White don’t exist in Color List-2.

**Read More:** **How to Use IF ISNA Function with VLOOKUP in Excel (3 Examples)**

**Similar Readings**

**VLOOKUP Not Working (8 Reasons & Solutions)****What Is a Table Array in VLOOKUP? (Explained with Examples)****INDEX MATCH vs VLOOKUP Function (9 Examples)****Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)****Excel VLOOKUP to Return Multiple Values Vertically**

## How to Compare Two Excel Sheets Using VLOOKUP Function

In this example, we will **compare two columns of two different Excel sheets using VLOOKUP**. The formula will be similar except in the **Table_array **argument there will be an addition of the worksheet name. Let`s say we have two lists of colors in two different worksheets. The worksheets’ name is defined as **CL-1** and **CL-2. **We will compare the** Color List-1 **of** CL-1 **worksheet with** Color List 2 **of the** CL-2 **worksheet. The two lists from the two worksheets are given below. It’s the List-1.

And it’s List-2.

**Steps:**

- In
**Cell C5**of the**CL-1**worksheet write the formula-

`=(VLOOKUP(B5,'CL-2'!$B$3:$B$9,1,FALSE))`

- Then hit the
**ENTER**button for the output.

- You will see the value Red appears. This is because the color Red is a common color in both the columns of
**CL-1**and**CL-2 worksheets.**Now copy this formula from**Cell C6**to**C11**by using the**Fill Handle**tool to find the result for the whole two columns.

After a while, all the output will appear.

Here the color Yellow, Orange, and Black don’t appear in **CL-2** worksheet. That’s why we get the rest **#N/A** in **CL-1** worksheet.

**Read More:** **VLOOKUP Example Between Two Sheets in Excel**

## How to Compare Two Columns Using VLOOKUP Function and Return a Third Value

We often need to use the **VLOOKUP** function for returning a third value by comparing two columns. In this section, we’ll learn how to do that in an easy way. To do it, we modified the dataset, connected some sizes with the colors, and added another column where we placed three colors. Now we’ll compare the color columns and return the size in the output column.

**Steps:**

- In
**Cell F5**, insert the following formula-

`=VLOOKUP(E5,$B$5:$C$10,2,FALSE)`

- After pressing the
**ENTER**button you will get the third value.

- To get the other values, just use the
**Fill Handle tool**.

Here are all the third values after comparing.

## Attention

It`s important to know the **VLOOKUP** function properly as any small mistake in the arguments will not give you the result you wanted. You can use the **MATCH** and **INDEX** functions as an alternative to the **VLOOKUP** functions.

## Conclusion

That’s all for the article. I hope the procedures described above will be good enough to compare/find matches in two columns in Excel using the **VLOOKUP **function. Feel free to ask any questions in the comment section and give me feedback. Visit the site to explore more.

**Further Readings**

**VLOOKUP to Return Multiple Columns in Excel (4 Examples)****VLOOKUP and Return All Matches in Excel (7 Ways)****Use of VBA VLOOKUP to Find Values from Another Worksheet in Excel****VLOOKUP to Search Text in Excel (4 Easy Ways)****VLOOKUP with Two Lookup Values in Excel (2 Approaches)****VLOOKUP to Return Multiple Values Horizontally in Excel**

Thanks for sharing this guide on Combining Excel Files. I have a Suggestion we can Also use Synkronizer Excel Add-in to Combine and Merge Multiple Excel Files into one file.