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

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

**Read More:** **How to Compare Three Columns in Excel Using VLOOKUP**

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

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

__Method-3__: Using Conditional Formatting

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

__Steps:__

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

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

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

**Similar Readings**

**How to Compare Text in Two Columns in Excel****Excel formula to compare two columns and return a value (5 examples)****How to Count Matches in Two Columns in Excel (5 Easy Ways)****Compare Two Columns or Lists in Excel (4 Suitable Ways)****How to Compare Two Columns for Finding Differences in Excel**

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

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

**Read More:** **Excel Formula to Compare and Return Value from Two Columns**

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

**Read More: ****How to Compare Multiple Columns Using VLOOKUP in Excel (5 Methods)**

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

**Read More:** **Match Two Columns and Output a Third in Excel (3 Quick Methods)**

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

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

**Read More: ****VLOOKUP Formula to Compare Two Columns in Different Excel Sheets**

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

## Further Readings

**How to Compare Two Columns and Return Common Values in Excel****Compare Two Columns in Excel and Highlight the Greater Value (4 Ways)****How to Compare Two Columns in Excel for Missing Values (4 ways)****Compare Three Columns in Excel and Return a Value(4 Ways)****How to Compare 3 Columns for Matches in Excel (4 Methods)**