How to Compare Two Columns Using VLOOKUP in Excel?

Comparing 2 Columns Using Vlookup

There would be times at which one needs to verify whether the data available in a particular tabulation is also present in a different tabulation which sometimes might be in a different sheet. While one can switch between the sheets and verify each item one after the other, it defeats the sole purpose of using MS Excel! What difference is this to verifying manually between the information recorded in two notebooks?

In this article, we would be covering a technique which will get the job done without much hassle and a formula will be put into use to make this happen – the VLOOKUP!

VLOOKUP – What it is?

Following is the syntax of Vlookup which contains all the basic constructs needed for its proper functioning,

=VLOOKUP(lookup_value, table_array, col_index_num, [range lookup])


  • lookup_value – the item which is to be searched elsewhere (source)
  • table_array – the destination table within which the item is to be searched
  • col_index_num – the column position from the start of the destination table whose value is to be returned
  • [range lookup] – an optional element set to FALSE (0) by default to return the value that exactly matches the search item. In case the requirement is to get an approximate match, this can be changed to TRUE (1).

With all these elements filled in, this formula shall return a value against a search item after thoroughly screening the selected destination table.

Comparing 2 Columns from Different Sheets in Excel

Let us consider the following data which has a list of items.

Sample Data 2
List of Items for Verification

There is an empty column at the end of the above tabulation titled ‘Verification’. So, what we are going to demonstrate is to verify whether each item listed in this tabulation is present in another list of data given in another sheet.

Data for Verification in ‘Sheet 4
Data for Verification in ‘Sheet 4’

From the above image, it is evident that the source list whose data is to be verified is in Sheet 4. Here’s another list in Sheet 3 which contains the sales data of the different items listed in Sheet 4.

Destination List in Sheet 3
Destination List in ‘Sheet 3’

Our objective here is to construct a Vlookup formula in the Sheet 3 list to compare it with the list in Sheet 4. Let us get started by double-clicking on Z9 in Sheet 4 to include an equals sign (=) followed by typing Vlookup and an open round parenthesis as shown below.

Constructing Vlookup
Constructing Vlookup Formula

The lookup value will be those in column Y which in this case would be ‘Chips’. So click on the cell Y2 and include a comma.

Selecting Lookup Value
Selecting Lookup Value

Now it is time to select the Table array & we shall get into sheet 3 to select the column with the items listed.

Column with Item Names Selected
Column with Item Names Selected

Now, remember to make this column selection an absolute reference since the selected column will change when this formula is copied & pasted to other cells due to the inherent auto-adjusting feature of MS Excel.

Absolute Reference
Table Array with Absolute Reference

Since we have selected only one column in Sheet 3, the column index number shall be ‘1’. Type ‘1’ after the comma following the table array & include another comma after typing it.

Column Index Number
Column Index Number Included

We would only be needing exact matches for these item names, so we shall choose FALSE. We can type ‘0’ too rather than typing FALSE in the formula & then close the round parenthesis.

Vlookup Constructed
Vlookup Constructed!

Hit ENTER & if ‘Chips’ is available in the list of items in Sheet 3, the result shall be ‘Chips’ else a #N/A (Not Available) error shall appear.

Chips Not Available
‘Chips’ Not Available in Sheet 3 Data

Copy this formula & paste it across all the cells below to know the results of whether they are available in the list in Sheet 3.

Comparison Completed
Comparison Completed!

It seems that S. No. 1, 7, 9 & 10 aren’t available in the Sheet 3 list.


Now we have reached the end of this article, hope it has provided you with sufficient clarity on comparing two columns using Vlookup. Have a look at this article, if you would like to know about using $ in formulae of MS Excel. For more cool tricks on using MS Excel, please do look into our space QuickExcel. Until then, Ciao!