At some point in your work with Excel, you might find yourself needing to compare two columns of data. Here, vlookup to compare two columns can identify differences. In this guide, we look at process of using VLOOKUP to compare two Excel data columns.
Table of Contents
Setting up your worksheet to Compare Two Columns in Excel Using VLOOKUP
Before we start with VLOOKUP, you should set up your sheet correctly. First, you should make sure that both columns of data are in the same worksheet or workbook. Then, columns must have unique headers to clearly identify the data they have.
Next, you should select the range of cells that contain your data. Such as including the headers. You can do this by clicking and dragging your mouse over the range. Or you can do it by using the keyboard shortcut “Ctrl+A” to select the entire worksheet.
comparing two columns in excel to find differences
Once everything is good to go, it is time to use VLOOKUP to compare the data. The VLOOKUP function searches for a value in the first column and returns a value in the same row from another column. These are all in the same table.
In case you want to use VLOOKUP for comparing two columns in excel to find differences, you need to specify the value you are searching for. Also, the range of cells you are searching through. Then, you should enter column number of the data you want to return. And whether you want an exact match or an approximate match.
vlookup for matching 2 columns
Here’s an example of how to use VLOOKUP to compare two columns of data.
- In a new column, you need to enter the VLOOKUP formula. The formula should start with “=VLOOKUP(“.
- Then, you should Specify the value searching for. In this case, we can use the first cell in the column we want to compare. For example, “=VLOOKUP(A2,”.
- Here you will specify the range of cells having data you want to be looked for. This should be the range of cells in the other column you want to compare to. Formula is like “=VLOOKUP(A2,B:C,”.
- So, one thing left to do is writing column number of the data you want to return. In this case, we want to return the value in the second column. So we will use “2”. Our formula now looks like this. “=VLOOKUP(A2,B:C,2,”.
- Finally, you need to specify whether you want an exact match or an approximate match. In our example, we want an exact match by typing FALSE. Ultimate formula is this. “=VLOOKUP(A2,B:C,2,FALSE)”.
After entering the formula, you can drag it down to compare all the cells. The result will show you whether there is a match or not.
how to use vlookup to compare 2 columns: Tips and Tricks
To compare two columns in Excel using VLOOKUP, you can follow these tips below.
- First, both columns should have a unique identifier. This can be a product code, customer ID or any other unique value. So you can use them to match the data in the two columns.
- In a new column, you will use the VLOOKUP function to search for the first value in the first column. The VLOOKUP function works by searching for a value in the first column of a table. And then, it is returning a corresponding value from a specified column. But they are all in the same row.
- If the value is found in the second column, the VLOOKUP function will return that value from the specified column. If the value is not found, the VLOOKUP function will return an error.
- After that, you can copy the formula down the entire length of the column. So you can look and compare all values in the first column with the second one.
- In case you need to identify the rows where the values in the first column are not found in the second column, you can use the IFERROR function to display a custom message instead of the error value.
For example, the VLOOKUP formula might look like this.
=VLOOKUP(A2,$B$2:$B$100,1,FALSE)
In this formula, A2 is the value being searched for in the second column. And $B$2:$B$100 is the range of cells containing the second column. While the 1 is the column number and FALSE specifies an exact match.
FAQ About comparing two columns in excel with vlookup
-
What is VLOOKUP in Excel?
VLOOKUP is a function can search for a specific value in a table or range of cells. And it will return a corresponding value from the same row in a different column.
-
How do I use VLOOKUP to compare two columns in Excel?
To use VLOOKUP to compare two columns in Excel, you need to specify a unique identifier that exists in both columns. Once you have unique identifier, you can use the VLOOKUP function to search for the value in one column and return wanted value from the same row in the other column.
-
What if there are missing values in one of the columns?
If there are missing values in one of the columns, the VLOOKUP function will return an error.
-
Can I use VLOOKUP to compare more than two columns?
Yes, you can use VLOOKUP to compare more than two columns in Excel. Here you will be nesting multiple VLOOKUP functions together. Also, you can use one VLOOKUP function to compare the first two columns. And then, you can write another VLOOKUP function to compare the results with a third column. This can go on like this.
Conclusion
As a general step guide, if you wanna compare two columns in Excel using VLOOKUP, follow below these steps and adjust to your case.
- First thing is opening your Excel worksheet and select the cell for results.
- Then you can click on Formulas tab at top.
- For our formula, you can click on Lookup & Reference section.
- Then you select VLOOKUP from dropdown menu.
- In Lookup_value field, you will select the first cell of the column for comparison (e.g., A2).
- For Table_array field, you should select the range of cells belonging second column to compare (e.g., B2:B10).
- Next one is about Col_index_num field. Here we should enter “1” since you want to return a value from the first column.
- For Range_lookup, you can enter FALSE to have exact match and press enter.
The results in the result column will show “N/A” for any values in the first column that do not match. And it will show the matching value from the second column for any values in the first column that do have a match.
A dedicated Career Coach, Agile Trainer and certified Senior Portfolio and Project Management Professional and writer holding a bachelor’s degree in Structural Engineering and over 20 years of professional experience in Professional Development / Career Coaching, Portfolio/Program/Project Management, Construction Management, and Business Development. She is the Content Manager of ProjectCubicle.
