Vlookup to Compare Two Columns – Show Differences in 3rd Column & More

Ways You Can Use Vlookup to Compare Two Columns for differences and matches

Often, people ask how they can use Vlookup to Compare Two Columns in Excel.

There are so many ways you can do this.

The method that you must use would depend on the information structure and what the user would want to get from it.

For instance, you may want to have a couple of columns compared and look for or highlight every matching data point in both columns.

You may also want to know about the differences wherein a data point is in a column and not included in the other, etc.

Since many people have asked us about this, we have decided to provide you with a tutorial that covers a lot of the possible scenarios.

Make sure that you pass this on to other people who use Excel if you find this a helpful lesson.

Take note that this tutorial has techniques in comparing columns that are not the only ones available.

According to your dataset, you may have to make adjustments or changes to the method.

Yet, the basic principles should be the same.

Whenever you think that you can add something to our tutorial, let us know about it in the comments!

Highlight Matches with Vlookup to Compare Two Columns

Whenever you want to highlight matching information and compare two columns, use conditional formatting’s duplicate functionality.

Take note that this may be different whenever you collate rows. Because of that, we will not compare according to rows.

Example: Highlight Data that is Mismatched and Have Two Columns Compared

Whenever you want to have the names in one list and not in the other become highlighted, you can have conditional formatting used.

  1. Choose the whole data set.
  2. Click on the Home tab.
  3. Check out the Styles group and click on the option’ Conditional Formatting.’                                               
  4. Make the cursor hover on the option Highlight Cell Rules.
  5. Tap on the Duplicate Values.                                                                                                                                                                           
  6. Make sure to select ‘Unique’ in the Duplicate Values dialog box.                                                                                                                       
  7. Specify the formatting to have the differences highlighted.                                                                                                                               
  8. Click on OK.

It will provide you with the result that is shown here. Highlighted here is every cell that includes the name that the other list doesn’t have.

Example: Have Matching Data Highlighted and Two Columns Compared

Whenever there are matches, you may often get datasets.

However, they may not be present in the same row. 

Here is an example of this:

Take note that column A has a list that is bigger compared to column B. Some of the names are present in both lists, but they are not found in the same row. These are Walmart, Adobe, and IBM.

Whenever you want to have every matching company name highlighted, you can do this through conditional formatting.

Do this with these steps:

  1. Choose the whole data set.
  2. Click on the Home tab.
  3. Check out the Styles group and then click on the option’ Conditional Formatting.’                                                             
  4. Make the cursor hover on the option Highlight Cell Rules.
  5. Tap on Duplicate Values.                                                                                                                                                                                                          
  6. Check out the dialog box of Duplicate Values and make sure to select ‘Duplicate.’                                                                                 
  7. Specify the desired formatting.                                                                                                                                                                                     
  8. Click on OK.

With these steps, you will get the result that is shown here:                                                                                                                                                 

Take note that the duplicate rule of Conditional Formatting is not case sensitive.

Because of that, ‘banana’ and ‘Banana’ are considered the same that they’ll get highlighted as duplicates.

Look for Missing Data Points and Compare the Two Columns

Whenever you want to know if a data point present in a list is in another list, you will have to use the lookup formulas.

Assume that you have this dataset and you want to know which companies are present in the A column but are unavailable in column B.

Use this VLOOKUP formula if you want to do this:

=ISERROR(VLOOKUP(A2,$B$2:$B$10,1,0))

With this formula, the VLOOKUP function is used so you can check if the company name in A is also in the B column or not.

It will provide the name from column B or return the #N/A error whenever it is available.

The names that will give the #N/A error are those that are unavailable in Column B.

The function ISERROR will return FALSE if there is no error and TRUE if the is an error in the VLOOKUP result.

Whenever you want to get the list of every name that doesn’t have a match, you can have the result column filtered to get all of the cells with TRUE.

You can also do the same by using the MATCH function;

=NOT(ISNUMBER(MATCH(A2,$B$2:$B$10,0)))

Please note that instead of the VLOOKUP, we prefer using the combination of MATCH/INDEX or the Match function as it is more powerful and flexible. 

Use Vlookup to Compare Two Columns with Matching Data Pulled

Whenever you have a couple of datasets and want to have the items of one list compared with the other and get the matching data point, you need to use the lookup formulas.

Example: Partial Pull of the Matching Data

You may have a dataset that has two columns with minor in their names differences. Using the Comparison of Two Columns For Exact Row Match would not work as you need to have the exact match to get the correct result.

You can get the approximate match option in the MATCH function or VLOOKUP, but you can use it here.

Pretend that you have this data set.

Note that the names are not completed in the second column as it has Exxon and JPMorgan instead of ExxonMobil and JPMorgan Chase.

For this case, you can have a partial lookup used with the use of the wildcard characters.

For this case, use the formula below to get the correct result:

=INDEX($A$2:$B$14,MATCH(“*”&D2&”*”,$A$2:$A$14,0),2)

or

=VLOOKUP(“*”&D2&”*”,$A$2:$B$14,2,0)

In this example, the wildcard character that is the asterisk (*) can have many characters represented.

Whenever the lookup value gets flanked with this on both sides, the first column’s value that includes the lookup value in the second column will be considered as its match.

As an example, *Exxon* is a match for ExxonMobil because * represents any amount of characters.

Example: Exact Pull of the Matching Data

As an example in the list below, I want to get the market valuation information for the second column.

You can do this by searching for that value in the first column and then fetching the corresponding value in market valuation.

Here is the formula that can help you do this:

=INDEX($A$2:$B$14,MATCH(D2,$A$2:$A$14,0),2)

or

=VLOOKUP(D2,$A$2:$B$14,2,0)

Comparison of Two Columns For An Exact Row Match

Here is a comparison in the simplest form.

For this, you have to do a comparison row by row to identify the rows with similar information and those that don’t have the same data.

Example: Use Matching Data to Highlight the Rows

Whenever you want the rows with matching data highlighted instead of having the result in another column, you can use Conditional Formatting to do this.

You can do this through these steps:

  1. Choose the whole dataset.
  2. Click on the tab ‘Home.’                                                                                                                                                                                      
  3. Check out the Styles group and click on the option’ Conditional Formatting.’                                                                                            
  4. Click on ‘New Rule’ from the drop-down.                                                                                                                                                                                                             
  5. Scan the dialog box ‘New Formatting Rule’ and click on the ‘Use a formula so you can determine the cells that you will format.’                         
  6. In the field formula, have this formula entered: =$A1=$B1                                                                                                                                 
  7. Tap on the Format button and then specify the format you want to apply to the matching cells.                                                                           
  8. Click on OK.

With this, all of the cells that have the same names in every row will be highlighted.

Example: Use of the IF Formula to Compare the Cells in the Same Row

If you want to have a more descriptive result, you can have the simple IF formula used as this can return a “Mismatch” every time the names are different and “Match” whenever they have the same names.

=IF(A2=B2,”Match”,”Mismatch”)

Take note that whenever you want the comparison to be case sensitive, this is the IF formula that you should use:

=IF(EXACT(A2,B2),”Match”,”Mismatch”)

The formula mentioned above would consider ‘ibm’ and ‘IBM’ as different names that would make the formula return ‘Mismatch.’

Example: Have Cells in the Same Row Compared

Here is a data set wherein I would have to check if the name in column A is similar or not with the one in column B.

Whenever it doesn’t match, I would need the result “FALSE.” If it matches, the outcome that I need is “TRUE.”

Here is the formula that can help you do this:

=A2=B2

Be Aware of How to Use Vlookup to Compare Two Columns – Show Differences in 3rd Column & More in Excel with ExcelMaster

Make yourself an Excel expert with us!

Get to know how to use Vlookup to compare two columns to show differences in the 3rd column and more in Excel with ExcelMaster NOW!