How to Compare Two Columns Using VLOOKUP Function in Excel

Get FREE Advanced Excel Exercises with Solutions!

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. Before starting the comparison, I will discuss the syntax, arguments, and other necessary things about the VLOOKUP function. So without any further delay, let’s start our journey.


Excel VLOOKUP Function: Overview

The V in the VLOOKUP function stands for “vertical”. The VLOOKUP is a built-in function of Excel that 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.


1. Using Excel VLOOKUP Function to Compare 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 1st column and lookup for this color in the 3rd 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.

Using Only VLOOKUP Function for Comparison Between Two Columns

Steps:

  • In Cell C2 write the formula-
=VLOOKUP(B5,$D$5:$D$10,1,FALSE)
  • Later, press the ENTER button for the output.

Insert Formula Using Only VLOOKUP Function for Comparison Between Two Columns

  • 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.

Use Fill Handle Tool for Comparison Between Two Columns

Here’s the whole comparison.

The #N/A results are found because the colors Blue and White are not in 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: How to Compare Three Columns Using VLOOKUP in Excel


2. Merging IF, ISNA, and VLOOKUP Formula to Compare 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.

Using IF, ISNA, and VLOOKUP for Comparison Between Two Columns

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.

Insert Formula Using IF, ISNA, and VLOOKUP for Comparison Between Two Columns

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 Compare 4 Columns in Excel VLOOKUP


3. Applying VLOOKUP Function Compare Two Columns of Different Excel Sheets

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 the 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.

How to Compare Two Excel Sheets Using VLOOKUP

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.

Formula to Compare Two Excel Sheets Using VLOOKUP

  • 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.

Use Fill Handle Tool to Compare Two Excel Sheets Using VLOOKUP

After a while, all the output will appear.

Here the colors 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: Excel Macro to Compare Two Columns


4. Using VLOOKUP Function to Compare Two Columns and Return a Third Value

We often need to use the VLOOKUP function to return 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.

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

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.

Formula to Compare Two Columns in Excel Using VLOOKUP Function and Return a Third Value

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

Here are all the third values after comparing.

Read More: VLOOKUP Formula to Compare Two Columns in Different Excel Sheets


Download Practice Workbook


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. Keep Excelling!


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Siam Hasan Khan
Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

1 Comment
  1. Reply
    Merge Excel Files Apr 2, 2019 at 3:00 PM

    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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo