Organizing data using various techniques is quite common in Microsoft Excel. Records are arranged in a tabular form here. There are ranges of data from multiple columns to compare. Multiple ways are there to use **the VLOOKUP function** in Excel to compare 4 columns. This article will discuss two approaches to compare 4 columns in Excel **using the VLOOKUP function**. It applies to both adjacent and non-adjacent columns and you can eventually modify it according to your requirements. All the methods have step-by-step instructions with a figurative description.

## π Download Excel File

Download the Excel file below.

## Learn to Compare 4 Columns Using VLOOKUP Function in Excel with These 2 Approaches

The article will introduce two ways to compare 4 columns using the VLOOKUP function in Excel. The following discussion will cover the uses of the **IF**, **IFERROR**, **AND**, **ISNA** along with the **VLOOKUP** function in two ways. Therefore, users will get a walkthrough of these solutions. You will find a dataset to modify in the following methods below to help you understand.

**approach**

### 1. Using IF and AND Functions with VLOOKUP Function

The most simple formula to compare 4 columns in Excel is using **the IF function**. **The AND function** merges two conditions or values so that both can participate at the same time. You can use **the ISNA function** to avoid errors.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- First, select cell
**H7**. - Second, enter the following formula here.

**=IF(ISNA(AND(VLOOKUP(E7,B7:B10,1,FALSE)<>ββ,VLOOKUP(F7,C7:C10,1,FALSE)<>β β)),ββ,βSameβ)**

- Third, hit the
**Enter**key.

- Fourth,
**copy the formulas down**their cells using the**Fill Handle**icon. - Fifth, you will find whether all three columns have the same values.

**π¨ Formula Breakdown**

**IF(ISNA(AND(VLOOKUP(E7,B7:B10,1,FALSE)<>ββ,VLOOKUP(F7,C7:C10,1,FALSE)<>β β)),ββ,βSameβ)**

πΒ Here,** VLOOKUP** looks for **E7** and **F7** from cell range **B7:B10** and **C7:C10** respectively.

πΒ Also,** AND** returns the result from the **VLOOKUP** function. Finally **AND** merges them to work together.

πΒ Then, **ISNA **returns FALSE if there is no error. In case of an error, it returns TRUE.

πΒ Finally,** IF** returns a blank cell if there is no match, returns β**Same**β otherwise.

You will get results as β**Same**β for similarities and blank cells otherwise cases.

**Β Read More: ****5 Ways to Compare Three Columns Using Vlookup in Excel**

**approach**

### 2. Utilizing IFERROR Function with VLOOKUP Function

**The IFERROR function** is an error-based function, You can merge this along with the **VLOOKUP** function to compare 4 columns in Excel. Following is a formula, where both functions are used multiple times.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- Initially, select cell
**H7**. - Next, enter the following formula here.

**=IFERROR(VLOOKUP(IFERROR(VLOOKUP(IFERROR(VLOOKUP(E7,B7:B10,1,FALSE),ββ),C7:C10,1,FALSE),ββ),F7:F10,1,FALSE),ββ)**

- Then, hit the
**Enter**key.

- Here, using the
**Fill Handle**icon, copy the formulas down to their cells. - Finally, you will find whether all 4 columns have equal values.

**π¨ Formula Breakdown**

**IFERROR(VLOOKUP(IFERROR(VLOOKUP(IFERROR(VLOOKUP(E7,B7:B10,1,FALSE),ββ),C7:C10,1,FALSE),ββ),F7:F10,1,FALSE),ββ)**

πΒ Here, **VLOOKUP** looks for **E7** from cell range **B7:B10**. You can use it multiple times to build formulas with the **IFERROR** function.

πΒ Also, the **IFERROR** function returns values from the condition or blank cell in case of error.

You will get results as the names of the product for similarities and blank cells otherwise cases.

**Β Read More: ****Excel Formula: Compare Two Columns and Return a Value**

## How to Compare 4 Columns in Excel Combining MATCH and CONCATENATE Functions

Apart from using the **VLOOKUP** function, you can also use **the MATCH function** and **the CONCATENATE function** to compare 4 columns in Excel as well.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- To begin with, select cell
**H7**. - Now, enter the following formula here.

**=ISNA(IF(MATCH(CONCATENATE(B7,C7),CONCATENATE($E$7:$E$10,$F$7:$F$10),0),TRUE,FALSE))**

- Next, hit the
**Enter**key.

- After that, copy the formulas down their cells using the
**Fill Handle**icon. - Finally, you will find the same results as
**FALSE**and unequal results as**TRUE**.

**π¨ Formula Breakdown**

**ISNA(IF(MATCH(CONCATENATE(B7,C7),CONCATENATE($E$7:$E$10,$F$7:$F$10),0),TRUE,FALSE))**

πΒ Here,** CONCATENATE** helps to join a specific amount of leading zeros with another data string. As it is joining **B7** and **C7**, it can also join cells in a respective cell range.

πΒ Also,** MATCH** searches for the **CONCATENATE **returns. It returns the search value with the exact result.

πΒ Again, **IF** returns a blank cell if there is no match, returns a result otherwise.

πΒ Finally,** ISNA** returns FALSE if there is no error. In case of an error, it returns TRUE.

As you can see matches can get the results as **FALSE** and the result comes as **TRUE** otherwise.

**Β Read More: ****4 Ways to Compare Three Columns in Excel and Return a Value**

## How to Compare 4 Columns in Excel Integrating INDEX and MATCH Functions

You can also merge **the MATCH function** and **the INDEX function** to compare 4 columns in Excel as well.

**β¬οΈβ¬οΈ STEPS β¬οΈβ¬οΈ**

- Initially, select cell
**H7**. - Next, enter the following formula here.

**=IF(ISNA(AND(INDEX($B$7:$B$10,MATCH(E7,$B$7:$B$10,0))<>ββ,INDEX($C$7:C$10,MATCH(F7,$C$7:$C$10,0))<>β β)),βFALSEβ,βTRUEβ)**

- Then, hit the
**Enter**key.

- Here, using the
**Fill Handle**icon, copy the formulas down to their cells. - Therefore, you will find out if all three columns have equal values or not.

**π¨ Formula Breakdown**

**IF(ISNA(AND(INDEX($B$7:$B$10,MATCH(E7,$B$7:$B$10,0))<>ββ,INDEX($C$7:C$10,MATCH(F7,$C$7:$C$10,0))<>β β)),βFALSEβ,βTRUEβ)**

πΒ Here,** MATCH(E7,$B$7:$B$10,0)** searched **E7** amidst range **B7** to **B10** and **F7** amidst range **C7** to **C10**. It returns the search value.

πΒ Also,** INDEX** gives you a reference at the intersection of rows or columns on range **B7:B10**.

πΒ Again, **AND** returns the result from the **INDEX** function. Finally **AND** merges them to work together.

πΒ Then, **ISNA** returns FALSE if there is no error. In case of an error, it returns TRUE.

πΒ Finally,** IF** returns a blank cell if there is no match, returns a result otherwise.

As you can see matches can get the results as **TRUE **and the result comes as **FALSE** otherwise.

**Β Read More: ****5 Ways to Compare 3 Columns for Matches in Excel**

## π Important Notes

ποΈΒ The **ISNA** function returns **TRUE** if it finds any error. Users should keep this in mind in case it might confuse them with the comparison.

ποΈΒ You can modify the formula if you want something specific if Excel finds a match.

## π Takeaways from This Article

The article lets the readers understand the variety of options available to compare 4 columns.

πΒ Primarily, you can compare using the **AND** and the **IF** functions to incorporate with the **VLOOKUP** function.

πΒ Although the **IFERROR** function is an error-ignoring function, it can easily help you with the comparison with the **VLOOKUP** function.

## Conclusion

To compare 4 columns in Excel, you can use the **VLOOKUP** function in two ways. You can use the **IF**, **AND**, and **IFERROR** functions in two ways. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow **ExcelDen** to get more access to solutions regarding your Excel problems.

## Related Articles

**4 Ways to Compare Two Columns in Excel for Missing Values****How to Count Matches in Two Columns in Excel**