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

## Compare 4 Columns in Excel Using VLOOKUP: 7 Effective Ways

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 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")`

**Formula Breakdown**

- This logical input
**COUNTIF(D5:D13, G5)>0**will return**1**when the texts in Combined 1 and Combined 2 match. - 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")`

**Formula Breakdown**

- This logical input
**AND(B5=E5, C5=F5)**will return**TRUE**when all the conditions become true. - 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.

__Steps:__

- Select the 4 columns excluding the 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`

- After that, select the
**Format**Option.

Then **Format Cells **Wizard will pop up

- Now, select the
**Fill**Option. - Then, 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 **Helper column**.

__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))`

**Formula Breakdown**

- 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")`

**Formula Breakdown**

- 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")`

**Formula Breakdown**

- 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 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**, and 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")`

**Formula Breakdown**

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

**Download Workbook**

## 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. Goodbye!

**<< Go Back to Columns | Compare | Learn Excel**