### Compare 2 columns using VLOOKUP

VLOOKUP function is used to compare two columns in Excel based on exact and partial match type. This function is designed to compare columns and to lookup, the data vertically as its name is derived from Vertical Lookup. Make sure the values in both columns must be unique, otherwise VLOOKUP will pick an only first match and will ignore the second occurrence of the lookup value.

The syntax of VLOOKUP function is as follows;

`=VLOOKUP (lookup_value, table_array, column_index_number, [range-lookup])`

On the basis of **column_index_number** we can retrieve the information related to ** lookup_value** in 2

^{nd}column where match found.

Suppose we have 2 columns of Invoice Numbers where 1^{st} column needs to compare with 2^{nd} column and VLOOKUP function returns those invoice numbers that are found in 2^{nd} column, on the basis of column-index_number and exact match type (Range_lookup). We will use this formula to do that.

`=VLOOKUP(A2,$B$2:$B$17,1,FALSE)`

Here, we will match each invoice number of 1^{st} list in column A with 2^{nd} list in column B and VLOOKUP will return the lookup_value of 1^{st} list that is found in 2^{nd} list. If lookup_value is not found in 2^{nd} list, VLOOKUP function will return #NA error.

Please have a look at how this comparison is performed using VLOOKUP function as per above formula.

Here,

**A2** is cell reference of lookup_value of 1^{st} list

**$B$2:$B$17** is table_array of 2^{nd} list

**1** is column_index_number as there is one column in table_array from which the matching value must be returned.

**FALSE** is range_lookup for exact match type

We will insert the formula in an active cell, like in C2 and will copy down to other cells corresponding to lookup-values of 1^{st} list in column A, as shown below.

Hence, VLOOKUP function will return all those invoice numbers of list 1 that are present in List 2, and will return #NA error that is not found in List 2.

### Compare 2 columns using MATCH

Excel MATCH function searches for a value in a range of cells (array) and returns the relative position of that value in that range. We can easily compare 2 columns in Excel using MATCH function based on exact and partial match types.

The syntax for MATCH function in Excel is;

`MATCH ( value, array, [match_type] )`

As we have two lists of invoices in our examples here, so ** value** is the lookup value from 1

^{st}list and

**is the range of cells in 2**

*array*^{nd}list, and

**is exact.**

*match_type*The formula to compare 2 columns using MATCH function is as follows.

`=MATCH(A2,$B$2:$B$17,0)`

This formula will return the relative position of matching the value in an array instead of the value itself and will return #NA error where there is no match. Apply this formula in the first active cell and then copy it down to other corresponding cells of the list.

Here, you can see MATCH function has returned #NA error for 1^{st} invoice number and returned relative position of 2^{nd} invoice number in list2.

### Compare 2 columns using COUNTIF

COUNTIF function in Excel is also used to compare 2 columns data by counting the occurrence of a value in a range of cells.

In this example, we can identify whether an invoice number is present in 2^{nd} column or not. If it returns 0 then there is no match, else the value has a match.

Combining IF and COUNTIF functions we can return the specified result if the given logical test is TRUE or FALSE, such as

If **COUNTIF($B$2:$B$17,A16)>0**, then return “**Matched**“, else return **FALSE**

Here is the formula to do that.

`=IF(COUNTIF($B$2:$B$17,A2)>0,"Matched")`

This will return the specified result **“Matched”** if the given logical test returns **TRUE**, else it will return **FALSE**.

### Compare 2 columns using INDEX, MATCH

Using a combination of INDEX, MATCH functions in Excel we can compare 2 columns data and also retrieve the matching values as VLOOKUP function does.

As we know that MATCH function returns the relative position of matched value, so we can use that relative position for row and column position required in INDEX function to retrieve the matching values in both columns, such as;

`=INDEX($B$2:$B$17,MATCH(A2,$B$2:$B$17,0))`

As the data in both columns are laid out in rows, so we will use MATCH function relative position for row number and will ignore for column number. This formula returns all those values from 2^{nd} column where MATCH function returns the relative position of 1^{st} column values in 2^{nd} column as shown below.

### Compare 2 columns using Conditional Formatting

Conditional Formatting is another option in Excel to compare 2 columns of data. We can highlight unique or duplicate values found in 2 columns. Here is the simplest way to do that;

Press **CTRL** key on the keyboard and select data of both columns. Click on **Conditional Formatting** feature from the Styles section at Home tab. From **Highlight Cells Rules**, choose **Duplicate Values**. A duplicate values dialog box appears and you can format cells that contain duplicate or unique values from a drop-down list with available formatting styles list or you can choose to customize style to highlight those cells. Press **OK** to confirm that changes.

The problem you are trying to solve could be more complex, if you are in a rush, or need a real Expert to help you save hours of struggle, click on this link to enter your problem and get connect to a qualified Excel expert in a few seconds. You can share your file and an expert will create a solution for you on the spot during an 1:1 live chat session. The first session is free.

## Leave a Comment