Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Compare 4 Columns in Excel VLOOKUP (Easiest 7 Ways)

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.


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.

Sample data table


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.

Dataset for using COUNTIF function to comapre 4 columns in Excel VLOOKUP

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

Combinig columns to comapre 4 columns in Excel VLOOKUP

  • Press ENTER.
  • Drag down the Fill Handle.

Using Fill Handle

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

Output obtaine dafter using Fill Handle to comapre 4 columns in Excel VLOOKUP

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

using formula to combine columns in Excel VLOOKUP

  • Press ENTER.
  • Drag down the Fill Handle.

Dragging Fill Handle

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

Outputs obayind after using Fill Handle to comapre 4 columns in Excel VLOOKUP

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.

using COUNTIF function in helper column

  • Press ENTER.
  • Drag down the Fill Handle.

using COUNTIF function to comapre 4 columns in Excel VLOOKUP

Result:
After that, you will get the following results where for not matching in these 4 columns you will get NOT MATCH.

Final output after comparing 4 columns in Excel VLOOKUP

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.

Sample dataset for using IF-AND function to compare 4 columns in Excel VLOOKUP

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.

using IF-AND function to compare 4 columns in Excel VLOOKUP

Step-02:

  • Press ENTER.
  • Drag down the Fill Handle.

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

Outputs got by using Fill Handle to compare 4 columns in Excel VLOOKUP

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.

Sample dataset for using Conditional Formatting to compare 4 columns in Excel VLOOKUP

Steps:

  • Select the 4 columns excluding Header.
  • Go to Home Tab>>Conditional Formatting Drop Down>>New Rule Option.

Creating New Rule using Conditional Formatting option

Then the New Formatting Rule Dialog Box will appear.

  • Select Use a formula to determine which cells to format Option.

Using formula to do Conditional Formatting to compare 4 columns in Excel VLOOKUP

  • Type the following formula in the Format values where this formula is true:
=$B5&$C5=$E5&$F5
  • After that, select the Format Option.

Inserting formula to do Conditional Formatting

Then Format Cells Wizard will pop up

  • Now, select the Fill Option.
  • Then, choose any color as per your choice.
  • Press OK.

Selecting Fill Option

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.

Output obtianed by using Conditional Formatting to compare 4 columns in Excel VLOOKUP

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


Similar Readings


Method-4: Using MATCH and CONCATENATE Function

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

Sample dataset using MATCH and CONCATENATE function to compare 4 columns in Excel VLOOKUP

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.

Using formula to compare 4 columns in Excel VLOOKUP

Step-02:

  • Press ENTER.
  • Drag down the Fill Handle.

Using Fill Handle

Result:
You will get TRUE for not matching up here because of using The ISNA function as below.

Output obtianed by using MATCH and CONCATENATE function to compare 4 columns in Excel VLOOKUP

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.

Dataset for using VLOOKUP function to compare 4 columns in Excel VLOOKUP

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.

Using formula to compare 4 columns in Excel VLOOKUP

Step-02:

  • Press ENTER.
  • Drag down the Fill Handle.

Utilizing Fill Handle

Result:
In this way, you will get the following results where for not matching in these 4 columns you will get FALSE.

Outputs got by using VLOOKUP function to compare 4 columns in Excel VLOOKUP

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.

Sample dataset for using INDEX-MATCH function to compare 4 columns in Excel VLOOKUP

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.

Inserting formula to compare 4 columns in Excel VLOOKUP

Step-02:

  • Press ENTER.
  • Drag down the Fill Handle.

Applying Fill Handle

Result:
After that, you will get the following results where for not matching in these 4 columns you will get FALSE.

Output obtianed after using INDEX-MATCH function

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.

Sample dataset for using IFERROR-VLOOKUP function to compare 4 columns in Excel VLOOKUP

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.
Using excel formula to compare 4 columns in Excel VLOOKUP

Step-02:

  • Press ENTER.
  • Drag down the Fill Handle.

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

Fimal output got after comparing 4 columns in Excel VLOOKUP

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.

Sample Practice Section provided in the Practice 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.


Further Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo