How to Compare 4 Columns in Excel VLOOKUP (7 Methods)

Dataset Overview

In this scenario, we have data from two companies, and we want to compare whether their product names and item colors match. We have a total of 4 columns, and our goal is to identify similarities. Let’s explore the methods for comparing these 4 columns in Excel.

Sample data table


Method 1 – Using the COUNTIF Function

  • Add Extra Columns:
    • Create three additional columns: Combined 1, Combined 2, and Helper.

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

  • Combine Text from Product and Color Columns:
    • In the Combined 1 column (starting from cell D5), enter the formula:
=B5&" "&C5

This formula concatenates the text from cells B5 and C5, separated by a space.

Combinig columns to comapre 4 columns in Excel VLOOKUP

    • Press ENTER and drag down the Fill Handle to apply the formula to all rows.

Using Fill Handle

All of the text 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

  • Combine Text from Other Columns:
    • In the Combined 2 column (starting from cell G5), enter the formula:
=E5&" "&F5

using formula to combine columns in Excel VLOOKUP

    • Press ENTER and drag down the Fill Handle to apply the formula.

Dragging Fill Handle

You will get the following results in the Combined 2 column.

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

  • Check for Matches:
    • In the Helper column (starting from cell H5), insert the formula:
=IF(COUNTIF(D5:D13,G5)>0,"MATCH","NOT MATCH")

Formula Breakdown

  • If the count of matching values between Combined 1 and Combined 2 is greater than 0, it returns MATCH; otherwise, it returns NOT MATCH.

using COUNTIF function in helper column

    • Press ENTER and drag down the fill handle to apply the formula to all rows.

using COUNTIF function to comapre 4 columns in Excel VLOOKUP

  • Result:
    • The Helper column will indicate whether the values match or not.

Final output after comparing 4 columns in Excel VLOOKUP


Method 2 – Using the IF-AND Function

  • Add an Extra Column:
    • Create an additional column called Helper.

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

  • Check for Matches:
    • In the Helper column (starting from cell G5), enter the formula:
=IF(AND(B5=E5,C5=F5),"MATCH","NOT MATCH")

Formula Breakdown

  • If both conditions (B5 equals E5 and C5 equals F5) are true, it returns MATCH; otherwise, it returns NOT MATCH.

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

    • Press ENTER and drag down the Fill Handle to apply the formula.

using Fill Handle

  • Result:
    • The Helper column will show whether the values match or not.

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


Method 3 – Using Conditional Formatting

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

  • Select the Columns:
    • Choose the 4 columns (excluding the header) that you want to compare.
  • Apply Conditional Formatting:
    • Go to the Home tab.
    • Click on the Conditional Formatting drop-down menu.
    • Choose New Rule.

Creating New Rule using Conditional Formatting option

    • In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.

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

    • Enter the following formula in the Format values where this formula is true:
=$B5&$C5=$E5&$F5
    • Click Format.

Inserting formula to do Conditional Formatting

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

Selecting Fill Option

  • Result:
    • The data table will highlight cells with matching values in these 4 columns using the chosen color.

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


Method 4 – Using the MATCH and CONCATENATE Functions

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

  • Add an Extra Helper Column:
    • Create an additional column called Helper.
  • Check for Matches:
    • In the Helper column (starting from cell G5), insert the formula:
=ISNA(IF(MATCH(CONCATENATE(B5,C5),CONCATENATE($E$5:$E$10,$F$5:$F$10),0),TRUE,FALSE))

Formula Breakdown

  • The CONCATENATE function combines text from the columns.
  • The MATCH function checks if the combined texts match between the two sets of columns.
  • The IF function returns TRUE if they match; otherwise, it returns FALSE.
  • The ISNA function handles cases where the value cannot be determined.

Using formula to compare 4 columns in Excel VLOOKUP

    • Press ENTER and drag down the Fill Handle.

Using Fill Handle

  • Result:
    • TRUE indicates non-matching values in these 4 columns.

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


Method 5 – Using the VLOOKUP Function

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

  • Add an Extra Helper Column:
    • Create another column called Helper.
  • Check for Matches:
    • In the Helper column (starting from cell G5), enter the 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 compares values in Column E with Column B and Column F with Column C.
  • The ISNA function handles cases where the value cannot be determined.

Using formula to compare 4 columns in Excel VLOOKUP

    • Press ENTER and drag down the Fill Handle.

Utilizing Fill Handle

  • Result:
    • FALSE indicates non-matching values in these 4 columns.

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


Method 6 – Using the INDEX-MATCH Function

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

  • Add an Extra Helper Column:
    • Create yet another column called Helper.
  • Check for Matches:
    • In the Helper column (starting from cell G5), insert the 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 looks up values in Column E and F.
  • The result is used by the INDEX function.
  • FALSE indicates non-matching values.

Inserting formula to compare 4 columns in Excel VLOOKUP

    • Press ENTER and drag down the Fill Handle.

Applying Fill Handle

  • Result:
    • FALSE indicates non-matching values.

Output obtianed after using INDEX-MATCH function


Method 7 – Using the IFERROR-VLOOKUP Function

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

  • Add an Extra Helper Column:
    • Create an additional column called Helper.
  • Check for Matches:
    • In the Helper column (starting from cell G5), insert 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

  • The innermost VLOOKUP compares values in Column E with Column B.
  • The next VLOOKUP compares the result with Column C.
  • The outermost VLOOKUP compares the overall result with Column F.
  • IFERROR handles cases where the value cannot be determined (replacing N/A with FALSE).
Using excel formula to compare 4 columns in Excel VLOOKUP

    • Press ENTER and drag down the Fill Handle.

Using the Fill Handle

  • Result:
    • FALSE indicates non-matching values in these 4 columns.
    • When the columns match, the corresponding text in the cells will appear.

Fimal output got after comparing 4 columns in Excel VLOOKUP


Practice Section

We have provided a Practice section like below in a sheet named “Practice”.

Sample Practice Section provided in the Practice Workbook


Download Workbook

You can download the practice workbook from here:


<< Go Back to Columns | Compare | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo