Excel is the most widely used application in corporate houses and business centers. We can easily process data using Excel. Sometimes we need to compare a certain number of columns or rows in Excel. In this article, we will discuss how to compare 4 columns in Excel.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

In the dataset, we have shown the names of students of different classes.

**6 Methods to Compare 4 Columns in Excel**

**1. Use Conditional Formatting to Compare 4 Columns**

We can compare 4 columns in Excel by using **Conditional Formatting**. We can find the duplicates by this method easily.

**Step 1:**

- Select the Cells of 4 Columns from the data set.

**Step 2:**

- Now, go to the
**Home**tab. - Select the
**Conditional Formatting**from the commands. - Select
**Duplicate Values**from**Highlight Cells Rules.**

**Step 3:**

- After selecting
**Duplicate Values,**we will get a**Pop-Up.** - From that
**Pop-Up**select**Duplicate Values**with our desired color.

**Step 4:**

- Finally, press
**OK**and get the return.

Here, we can see that duplicate cells are colored after comparing the given 4 columns.

**2. Use AND Function to Compare 4 Columns in Excel**

In this section, we will use **the AND function** to compare columns in Excel.

The **AND** function is one of the logical functions. It is used to determine if all conditions in a test are **TRUE** or not. The **AND **function returns **TRUE** if all its arguments evaluate **TRUE**, and returns **FALSE** if one or more arguments evaluate to **FALSE**.

**Syntax:**

**AND(logical1, [logical2], â€¦)**

**Argument:**

*logical1 ***â€“ **The first condition that we want to test can evaluate as either **TRUE **or **FALSE**.

** logical2, â€¦** â€“ Additional conditions that you want to test that can evaluate to either

**TRUE**or

**FALSE**, up to a maximum of 255 conditions.

Here, we will compare four columns and check if all the columns of a certain row are the same or not.

#### 2.1 AND Function with Cells

**Step 1:**

- First, add a column named
**Match**in our data set.

**Step 2:**

- Now, type the
**AND**function and compare each of the 4 columns cells one by one. The formula is:

`=AND(B5=C5,C5=D5,D5=E5)`

**Step 3:**

- Now, press
**Enter**.

**Step 4:**

- Now, drag the
**Fill Handle**icon to the end.

**2.2 AND Function with Range**

We can apply an array function by this **AND **function and use only the range instead of using cells separately.

**Step 5:**

- Modify the
**AND**function. So, the formula will look like this:

`=AND(B5=C5:E5)`

**Step 6:**

- Now, press
**Ctrl+Shift+Enter**, because this is an array function.

**Step 7:**

- Now, drag the
**Fill Handle**icon to the last.

We see that only the **9th row **of all the columns contains the same content. Thatâ€™s why the Match result is **TRUE **and **FALSE **for the rest.

**3. Compare 4 Columns with COUNTIF in Excel**

**The COUNTIF function** is one of the statistical functions It is used to count the number of cells that meet a criterion.

**Syntax:**

**COUNTIF(range, criteria)**

**Argument:**

** range **â€“ It is the group of cells we will count. The range can contain numbers, arrays, a named range, or references that contain numbers. Blank and text values are ignored.

** criteria **â€“ It may be a number, expression, cell reference, or text string that determines which cells will be counted.

**COUNTIF**uses only a single criterion.

**Step 1:**

- Go to
**Cell F5**. - Write the
**COUNTIF**function. The formula is:

`=COUNTIF(B5:E5,B5)=4`

**Step 2:**

- Then press
**Enter**.

**Step 3:**

- Pull the
**Fill Handle**to**Cell F9**.

We can also apply **COUNTIF **in another way.

**Step 4:**

- Modify the
**COUNTIF**function on**Cell F5**. The formula is:

`=COUNTIF(B5:E5,"<>"&B5)=0`

**Step 5:**

- Then press
**Enter**.

**Step 6:**

- Pull the
**Fill Handle**icon to the last cell.

We see that in the 9th row showing **TRUE **as all these rows are the same for all the columns.

**4. Insert VLOOKUP to Compare 4 Columns**

**The VLOOKUP function** is used when we need to find things in a table or a range by row.

**Syntax:**

**VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])**

**Argument:**

** lookup_value** â€“ The value we want to look up. The value we want to look up must be in the first column of the range of cells we specify in the

**table_array**argument.

**Lookup_value**can be a value or a reference to a cell.

** table_array** â€“ The range of cells in which the

**VLOOKUP**will search for the lookup_value and the return value. We can use a named range or a table, and you can use names in the argument instead of cell references.

**col_index_num** â€“ The column number (starting with 1 for the left-most column of **table_array**) that contains the return value.

** range_lookup** â€“ A logical value that specifies whether we want

**VLOOKUP**to find an approximate or an exact match.

To apply we need to modify our data set. This is our new data set.

Here, we will search for a specific student name and in return some information about that student from our mentioned 4 columns.

**Step 1:**

- First, we set a criteria option in our data set.
- Select
**John**as our criteria.

**Step 2:**

- Now, type the
**VLOOKUP**function in**Cell D13**. - Here, we will search
**Cell D12**from the range and get values of the 4th column Named**Grade**. So, the formula will be:

**Step 3:**

Now, press **Enter**.

**Read more:** **How to Compare 4 Columns in Excel VLOOKUP**

**5. Combination of MATCH & INDEX Functions in Excel**

**The INDEX function** returns a value or the reference to a value from within a table or range.

**Syntax:**

**INDEX(array, row_num, [column_num])**

**Argument:**

** array **â€“ A range of cells or an array constant.

If an array contains only one row or column, the corresponding row_num or column_num argument is optional.

If the array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in the array.

** row_num **â€“ It selects the row in the array from which to return a value. If row_num is omitted, column_num is required.

** column_num** â€“ It selects the column in the array from which to return a value. If column_num is omitted, row_num is required.

**The MATCH function** searches for a specified item in a range of cells and then returns the relative position of that item in the range.

**Syntax:**

**MATCH(lookup_value, lookup_array, [match_type])**

**Argument:**

** lookup_value** Â Â â€“ It is the value that we want to match in lookup_array. The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

** lookup_array** â€“ The range of cells where we search.

** match_type**Â â€“ The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.

**Step 1:**

- First, we set
**Jeo**as the criteria in**Cell D12**.

**Step 2:**

- Put the combination of the
**INDEX**and**MATCH**formula in**Cell D13**. The formula is:

`=INDEX(D5:D9,MATCH(D12,B5:B9,0))`

**Step 3:**

- Now, press
**Enter**.

We get the return of our mentioned condition.

**6. Combination of AND & EXACT Functions in Excel**

**The EXACT function**Â compares two text strings and returns **TRUE** if they are exactly the same, **FALSE **otherwise. **EXACT **is case-sensitive but ignores formatting differences. Use **EXACT** to test text being entered into a document.

**Syntax:**

**EXACT(text1, text2)**

**Arguments:**

** text1 â€“** The first text string.

** text2 **Â â€“ The second text string.

In this method, we will apply the combination of **AND** & **EXACT** functions.

**Step 1:**

- Go to
**Cell F5**. - Write the formula containing
**AND**&**EXACT**function. So, the formula will be:

`=AND(EXACT(B5:E5,B5))`

**Step 2:**

- Now, press
**Enter**.

**Step 3:**

- Pull the
**Fill Handle**icon to the last.

**Conclusion**

In this article, we described a method to explain how to compare 4 columns in Excel. I hope this will satisfy your needs. Please have a look at our website **Exceldemy.com** and give your suggestions in the comment box.