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