How to Compare 4 Columns in Excel (6 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

how to compare 4 columns in excel


How to Compare 4 Columns in Excel: 6 Effective Methods

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.

Steps:

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

Use Conditional Formatting to Compare 4 Columns

  • 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

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

  • 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.

Read More: How to Compare 3 Columns for Matches in Excel


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

Steps:

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

Use AND Function to Compare 4 Columns in Excel

  • 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

  • Now, press Enter.


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.

Steps:

  • Modify the AND function. So, the formula will look like this:
=AND(B5=C5:E5)

Use AND Function to Compare 4 Columns in Excel

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

  • 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 Function 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.

Steps:

  • Go to Cell F5.
  • Write the COUNTIF function. The formula is:
=COUNTIF(B5:E5,B5)=4

Compare 4 Columns with COUNTIF in Excel

  • Then press Enter.

  • Pull the Fill Handle to Cell F9.

Compare 4 Columns with COUNTIF in Excel

We can also apply COUNTIF in another way.

Steps:

  • Modify the COUNTIF function on Cell F5. The formula is:
=COUNTIF(B5:E5,"<>"&B5)=0

Compare 4 Columns with COUNTIF in Excel

  • Then press Enter.

  • Pull the Fill Handle icon to the last cell.

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


4. Insert VLOOKUP Function 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.

Steps:

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

  • 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

  • Now, press Enter.

Insert VLOOKUP to Compare 4 Columns


5. Combine 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.

Steps:

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

  • 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

  • Now, press Enter.

Combination of MATCH & INDEX Function in Excel

We get the return of our mentioned condition.


6. Merge 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.

Steps:

  • Go to Cell F5.
  • Write the formula containing AND & EXACT functions. So, the formula will be:
=AND(EXACT(B5:E5,B5))

Combination of AND & EXACT Function in Excel

  • Now, press Enter.

Combination of AND & EXACT Function in Excel

  • Pull the Fill Handle icon to the last.


Download Practice Workbook

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


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 give your suggestions in the comment box and share the articles with your friends and colleagues. Goodbye!


<< Go Back to Columns | Compare | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo