How to Compare 4 Columns in Excel (6 Methods)

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.

Data set for compare 4 columns in Excel


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.

Use Conditional Formatting to Compare 4 Columns

Step 2:

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

Use Conditional Formatting to Compare 4 Columns

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.

Use Conditional Formatting to Compare 4 Columns

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.

Use AND Function to Compare 4 Columns in Excel

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)

Use AND Function to Compare 4 Columns in Excel

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)

Use AND Function to Compare 4 Columns in Excel

Step 6:

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

Step 7:

  • Now, drag the Fill Handle icon to the last.

Use AND Function to Compare 4 Columns in Excel

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

Compare 4 Columns with COUNTIF in Excel

Step 2:

  • Then press Enter.

Step 3:

  • Pull the Fill Handle to Cell F9.

Compare 4 Columns with COUNTIF in Excel

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

Compare 4 Columns with COUNTIF in Excel

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.

Insert VLOOKUP to Compare 4 Columns

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:

Insert VLOOKUP to Compare 4 Columns

Step 3:

Now, press Enter.

Insert VLOOKUP to Compare 4 Columns

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))

Combination of MATCH & INDEX Function in Excel

Step 3:

  • Now, press Enter.

Combination of MATCH & INDEX Function in Excel

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))

Combination of AND & EXACT Function in Excel

Step 2:

  • Now, press Enter.

Combination of AND & EXACT Function in Excel

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.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo