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.

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.

Steps:

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

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

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

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

Read More: Compare Two Columns in Excel and Highlight the Greater Value (4 Ways)

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.

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

• Now, press Enter.

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

Steps:

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

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

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

Read More: How to Compare Two Columns in Excel for Match (8 ways)

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:

• Then press Enter.

• Pull the Fill Handle to Cell F9.

We can also apply COUNTIF in another way.

Steps:

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

• Then press Enter.

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

Read More: How to Count Matches in Two Columns in Excel (5 Easy Ways)

Similar Readings

• How to Compare Multiple Columns Using VLOOKUP in Excel (5 Methods)
• VLOOKUP Formula to Compare Two Columns in Different Excel Sheets
• How to Compare Two Columns in Excel for Missing Values (4 ways)
• Match Two Columns and Output a Third in Excel (3 Quick Methods)
• How to Compare Two Columns and Return Common Values in Excel

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.

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:

• Now, press Enter.

Read More: How to Compare 4 Columns in Excel VLOOKUP (Easiest 7 Ways)

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:

• Now, press Enter.

We get the return of our mentioned condition.

Read More: How to Match Multiple Columns in Excel (Easiest 5 ways)

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:

• Now, press Enter.

• Pull the Fill Handle icon to the last.

Read More: Macro to Compare Two Columns in Excel and Highlight Differences

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

• How to Compare 3 Columns for Matches in Excel (4 Methods)
• Excel Formula to Compare and Return Value from Two Columns
• How to Compare Text in Two Columns in Excel
• Compare Three Columns in Excel and Return a Value(4 Ways)
• How to Compare Two Lists and Return Differences in Excel
• How to Select Specific Columns in Excel (4 Easy Methods)