If you are looking for some of the easiest ways to compare 4 columns in Excel VLOOKUP, then you are in the right place. Sometimes it becomes necessary to compare 4 columns for matching up similar data in these columns. By following this article, it will be easier for you to compare 4 columns easily in Excel.

**Table of Contents**hide

## Download Workbook

## 7 Ways to Compare 4 columns in Excel VLOOKUP

Here, I have some data from two companies to compare if their **Product** and **Color **of items are similar or not. So, I have a total of 4 columns, and I want to compare these columns and find out the similarities. Let’s dive into the article and get to know the ways of comparing 4 columns in Excel.

__Method-1__: Comparing 4 columns Using COUNTIF Function

You can compare 4 columns using **the COUNTIF function **and the** Logical IF function**. At first, you have to add some extra columns; **Combined 1, Combined 2, Helper**. Then maintain the following steps.

__Step-01__**:**

**➤**Select the output

**Cell D5**in the

**Combined 1 column**.

**➤**Type the following formula

`=B5&" "&C5`

Here, **& **will join the texts in **Cell** **B5 **and **C5**

**➤**Press **ENTER**

**➤**Drag down the

**Fill Handle**

Then all of the texts of the **Product **and **Color column **will be combined in the **Combined 1 column.**

__Step-02__**:**

**➤**Select the output

**Cell G5**in the

**Combined 2 column**.

**➤**Type the following formula

`=E5&" "&F5`

Here, **& **will join the texts in **Cell** **E5 **and **F5**

**➤**Press **ENTER**

**➤**Drag down the

**Fill Handle**

Then you will get the following results in the **Combined 2 column**.

__Step-03__**:**

**➤**Select the output

**Cell H5**in the

**Helper column**.

**➤**Type the following formula

`=IF(COUNTIF(D5:D13,G5)>0,"MATCH","NOT MATCH")`

This logical input **COUNTIF(D5:D13, G5)>0 **will return **1 **when the texts in **Combined 1** and **Combined 2** match, and then **the IF function** will return **MATCH** otherwise **NOT MATCH**.

**➤**Press **ENTER**

**➤**Drag down the

**Fill Handle**

__Result__**:**After that, you will get the following results where for not matching in these 4 columns you will get

**NOT MATCH**.

__Method-2__: Comparing 4 columns Using IF-AND Function

You can compare 4 columns using the** Logical IF function **and **the AND function**. You have to add an extra column **Helper**.

__Step-01__**:**

**➤**Select the output

**Cell G5**in the

**Helper column**.

**➤**Type the following formula

`=IF(AND(B5=E5,C5=F5),"MATCH","NOT MATCH")`

This logical input **AND(B5=E5, C5=F5) **will return **TRUE **when all the conditions become true, and then **the IF function** will return **MATCH** otherwise **NOT MATCH**.

__Step-02__**:**

**➤**Press

**ENTER**

**➤**Drag down the

**Fill Handle**

__Result__**:**In this way, you will get the following results where for not matching in these 4 columns you will get

**NOT MATCH**.

__Method-3__: Using Conditional Formatting

You can compare 4 columns using **Conditional Formatting **easily.

__Step-01__**:**

**➤**Select the 4 columns excluding Header

**➤**Go to

**Home**Tab>>

**Conditional Formatting**Drop Down>>

**New Rule**Option

Then the **New Formatting Rule **Dialog Box will appear.**➤**Select **Use a formula to determine which cells to format **Option

**➤**Type the following formula in the **Format values where this formula is true:**

`=$B5&$C5=$E5&$F5`

**➤**Select the **Format **Option

Then **Format Cells **Wizard will pop up**➤**Select the **Fill **Option**➤**Choose any color as per your choice**➤**Press **OK**.

__Result__**:**After that, you will get a data table where the same value containing cells in these 4 columns will be filled up with the color.

__Method-4__: Using MATCH and CONCATENATE Function

You can compare 4 columns using **the CONCATENATE function**, **the MATCH func****tion**, **the IF function**, and **the ISNA function. **You have to add an extra column** Helper**.

__Step-01__**:**

**➤**Select the output

**Cell G5**in the

**Helper column**.

**➤**Type the following formula

`=ISNA(IF(MATCH(CONCATENATE(B5,C5),CONCATENATE($E$5:$E$10,$F$5:$F$10),0),TRUE,FALSE))`

**The CONCATENATE function **will join up the texts in the columns**The MATCH function **will determine whether the combined texts of the two columns will match up with the combined texts of the other two columns.

**The IF function **will give **TRUE** if the texts in the columns match otherwise **FALSE**

**The ISNA function**checks whether the value is determined or not. If the value cannot be determined or does not match then it will return

**TRUE**otherwise

**FALSE**

__Step-02__**:**

**➤**Press

**ENTER**

**➤**Drag down the

**Fill Handle**

__Result__**:**You will get

**TRUE**for not matching up here because of using

**The ISNA function**as below.

__Method-5__: Using VLOOKUP Function

You can compare 4 columns using **the VLOOKUP function**, **the IF function**, and **the ISNA function**. You have to add an extra column **Helper**.

__Step-01__**:**

**➤**Select the output

**Cell G5**in the

**Helper column**.

**➤**Type the following formula

`=IF(ISNA(AND(VLOOKUP(E5,$B$5:$B$13,1,FALSE)<>" ",VLOOKUP(F5,$C$5:$C$13,1,FALSE)<>" ")),"FALSE","TRUE")`

**The VLOOKUP function **will look up the value in Cell **E5 **in **Column B **and the value in Cell **F5 **in **Column C**.

If the value cannot be determined or does not match then **The ISNA function** will return **TRUE **otherwise **FALSE****The IF function **will convert **TRUE **to **FALSE **and **FALSE ** to **TRUE**.

__Step-02__**:**

**➤**Press

**ENTER**

**➤**Drag down the

**Fill Handle**

__Result__**:**In this way, you will get the following results where for not matching in these 4 columns you will get

**FALSE**.

__Method-6__: Using INDEX-MATCH Function

You can compare 4 columns using **the MATCH function**, **the INDEX function, the IF function**, and **the ISNA function**. You have to add an extra column **Helper**.

__Step-01__**:**

**➤**Select the output

**Cell G5**in the

**Helper column**.

**➤**Type the following formula

`=IF(ISNA(AND(INDEX($B$5:$B$13,MATCH(E5,$B$5:$B$13,0))<>" ",INDEX($C$5:C$13,MATCH(F5,$C$5:$C$13,0))<>" ")),"FALSE","TRUE")`

**The MATCH function **will look up the value in Cell **E5 **in **Column B **and the value in Cell **F5 **in **Column C**.

Then the result will be used by **the INDEX function**

__Step-02__**:**

**➤**Press

**ENTER**

**➤**Drag down the

**Fill Handle**

__Result__**:**After that, you will get the following results where for not matching in these 4 columns you will get

**FALSE**.

__Method-7__: Using IFERROR-VLOOKUP Function

Suppose, now you have to compare between the **Old Products** and **New Arrival** columns of the two companies. That means all of the four columns contain the same type of items.

Here, I will compare 4 columns using **the IFERROR function**, **the VLOOKUP function**. You have to add an extra column **Helper**.

__Step-01__**:**

**➤**Select the output

**Cell G5**in the

**Helper column**.

**➤**Type the following formula

`=IFERROR(VLOOKUP(IFERROR(VLOOKUP(IFERROR(VLOOKUP(E5,$B$5:$B$13,1,FALSE),"FALSE"),$C$5:$C$13,1,FALSE),"FALSE"),$F$5:$F$10,1,FALSE),"FALSE")`

**VLOOKUP(E5,$B$5:$B$13,1, FALSE **is the comparison of **Column E **and **Column B**

**$C$5:$C$13**is the range to lookup against the value gained by the previous comparison.

Then this value will be compared with the range

**$F$5:$F$10**

**IFERROR**is used to replace

**N/A**with

**FALSE**

__Step-02__**:**

**➤**Press

**ENTER**

**➤**Drag down the

**Fill Handle**

__Result__**:**After that, you will get the following results where for not matching in these 4 columns you will get

**FALSE**. And when the columns have matched the text in the cells will appear then.

## Practice Section

For doing practice by yourself we have provided a** Practice** section like below in a sheet named “**Practice**”. Please do it by yourself.

## Conclusion

In this article, I tried to cover the easiest ways to compare 4 columns in Excel VLOOKUP effectively. Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.