2 Ways to Compare 4 Columns Using VLOOKUP Function in Excel

Organizing data using various techniques is quite common in Microsoft Excel. Records are arranged in a tabular form here. There are ranges of data from multiple columns to compare. Multiple ways are there to use the VLOOKUP function in Excel to compare 4 columns. This article will discuss two approaches to compare 4 columns in Excel using the VLOOKUP function. It applies to both adjacent and non-adjacent columns and you can eventually modify it according to your requirements. All the methods have step-by-step instructions with a figurative description.


πŸ“ Download Excel File

Download the Excel file below.


Learn to Compare 4 Columns Using VLOOKUP Function in Excel with These 2 Approaches

The article will introduce two ways to compare 4 columns using the VLOOKUP function in Excel. The following discussion will cover the uses of the IF, IFERROR, AND, ISNA along with the VLOOKUP function in two ways. Therefore, users will get a walkthrough of these solutions. You will find a dataset to modify in the following methods below to help you understand.

Dataset for comparing 4 columns using VLOOKUP


approach

1. Using IF and AND Functions with VLOOKUP Function

The most simple formula to compare 4 columns in Excel is using the IF function. The AND function merges two conditions or values so that both can participate at the same time. You can use the ISNA function to avoid errors.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell H7.
  • Second, enter the following formula here.

=IF(ISNA(AND(VLOOKUP(E7,B7:B10,1,FALSE)<>””,VLOOKUP(F7,C7:C10,1,FALSE)<>” β€œ)),””,”Same”)
  • Third, hit the Enter key.

Using IF and AND functions with VLOOKUP Function to compare 4 columns

  • Fourth, copy the formulas down their cells using the Fill Handle icon.
  • Fifth, you will find whether all three columns have the same values.

Using IF and AND functions with VLOOKUP to compare 4 columns in Excel

πŸ”¨ Formula Breakdown

IF(ISNA(AND(VLOOKUP(E7,B7:B10,1,FALSE)<>””,VLOOKUP(F7,C7:C10,1,FALSE)<>” β€œ)),””,”Same”)

πŸ‘‰Β  Here, VLOOKUP looks for E7 and F7 from cell range B7:B10 and C7:C10 respectively.

πŸ‘‰Β  Also, AND returns the result from the VLOOKUP function. Finally AND merges them to work together.

πŸ‘‰Β  Then, ISNA returns FALSE if there is no error. In case of an error, it returns TRUE.

πŸ‘‰Β  Finally, IF returns a blank cell if there is no match, returns β€œSame” otherwise.

You will get results as β€œSame” for similarities and blank cells otherwise cases.

πŸ“•Β Read More: 5 Ways to Compare Three Columns Using Vlookup in Excel


approach

2. Utilizing IFERROR Function with VLOOKUP Function

The IFERROR function is an error-based function, You can merge this along with the VLOOKUP function to compare 4 columns in Excel. Following is a formula, where both functions are used multiple times.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell H7.
  • Next, enter the following formula here.

=IFERROR(VLOOKUP(IFERROR(VLOOKUP(IFERROR(VLOOKUP(E7,B7:B10,1,FALSE),””),C7:C10,1,FALSE),””),F7:F10,1,FALSE),””)
  • Then, hit the Enter key.

Utilizing IFERROR function with VLOOKUP Function to compare 4 columns

  • Here, using the Fill Handle icon, copy the formulas down to their cells.
  • Finally, you will find whether all 4 columns have equal values.

πŸ”¨ Formula Breakdown

IFERROR(VLOOKUP(IFERROR(VLOOKUP(IFERROR(VLOOKUP(E7,B7:B10,1,FALSE),””),C7:C10,1,FALSE),””),F7:F10,1,FALSE),””)

πŸ‘‰Β  Here, VLOOKUP looks for E7 from cell range B7:B10. You can use it multiple times to build formulas with the IFERROR function.

πŸ‘‰Β  Also, the IFERROR function returns values from the condition or blank cell in case of error.

You will get results as the names of the product for similarities and blank cells otherwise cases.

πŸ“•Β Read More: Excel Formula: Compare Two Columns and Return a Value


How to Compare 4 Columns in Excel Combining MATCH and CONCATENATE Functions

Apart from using the VLOOKUP function, you can also use the MATCH function and the CONCATENATE function to compare 4 columns in Excel as well.

⬇️⬇️ STEPS ⬇️⬇️

  • To begin with, select cell H7.
  • Now, enter the following formula here.

=ISNA(IF(MATCH(CONCATENATE(B7,C7),CONCATENATE($E$7:$E$10,$F$7:$F$10),0),TRUE,FALSE))
  • Next, hit the Enter key.

Combining MATCH and CONCATENATE functions to compare 4 columns

  • After that, copy the formulas down their cells using the Fill Handle icon.
  • Finally, you will find the same results as FALSE and unequal results as TRUE.

πŸ”¨ Formula Breakdown

ISNA(IF(MATCH(CONCATENATE(B7,C7),CONCATENATE($E$7:$E$10,$F$7:$F$10),0),TRUE,FALSE))

πŸ‘‰Β  Here, CONCATENATE helps to join a specific amount of leading zeros with another data string. As it is joining B7 and C7, it can also join cells in a respective cell range.

πŸ‘‰Β  Also, MATCH searches for the CONCATENATE returns. It returns the search value with the exact result.

πŸ‘‰Β  Again, IF returns a blank cell if there is no match, returns a result otherwise.

πŸ‘‰Β  Finally, ISNA returns FALSE if there is no error. In case of an error, it returns TRUE.

As you can see matches can get the results as FALSE and the result comes as TRUE otherwise.

πŸ“•Β Read More: 4 Ways to Compare Three Columns in Excel and Return a Value


How to Compare 4 Columns in Excel Integrating INDEX and MATCH Functions

You can also merge the MATCH function and the INDEX function to compare 4 columns in Excel as well.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell H7.
  • Next, enter the following formula here.
=IF(ISNA(AND(INDEX($B$7:$B$10,MATCH(E7,$B$7:$B$10,0))<>””,INDEX($C$7:C$10,MATCH(F7,$C$7:$C$10,0))<>” β€œ)),”FALSE”,”TRUE”)
  • Then, hit the Enter key.

Combining MATCH and INDEX functions to compare 4 columns

  • Here, using the Fill Handle icon, copy the formulas down to their cells.
  • Therefore, you will find out if all three columns have equal values or not.

πŸ”¨ Formula Breakdown

IF(ISNA(AND(INDEX($B$7:$B$10,MATCH(E7,$B$7:$B$10,0))<>””,INDEX($C$7:C$10,MATCH(F7,$C$7:$C$10,0))<>” β€œ)),”FALSE”,”TRUE”)

πŸ‘‰Β  Here, MATCH(E7,$B$7:$B$10,0) searched E7 amidst range B7 to B10 and F7 amidst range C7 to C10. It returns the search value.

πŸ‘‰Β  Also, INDEX gives you a reference at the intersection of rows or columns on range B7:B10.

πŸ‘‰Β  Again, AND returns the result from the INDEX function. Finally AND merges them to work together.

πŸ‘‰Β  Then, ISNA returns FALSE if there is no error. In case of an error, it returns TRUE.

πŸ‘‰Β  Finally, IF returns a blank cell if there is no match, returns a result otherwise.

As you can see matches can get the results as TRUE and the result comes as FALSE otherwise.

πŸ“•Β Read More: 5 Ways to Compare 3 Columns for Matches in Excel


πŸ“„ Important Notes

πŸ–ŠοΈΒ  The ISNA function returns TRUE if it finds any error. Users should keep this in mind in case it might confuse them with the comparison.

πŸ–ŠοΈΒ  You can modify the formula if you want something specific if Excel finds a match.


πŸ“ Takeaways from This Article

The article lets the readers understand the variety of options available to compare 4 columns.

πŸ“ŒΒ  Primarily, you can compare using the AND and the IF functions to incorporate with the VLOOKUP function.

πŸ“ŒΒ  Although the IFERROR function is an error-ignoring function, it can easily help you with the comparison with the VLOOKUP function.


Conclusion

To compare 4 columns in Excel, you can use the VLOOKUP function in two ways. You can use the IF, AND, and IFERROR functions in two ways. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.


Related Articles

(Visited 61 times, 1 visits today)
Farihat

Farihat

An Excel enthusiast who wants to share and loves to solve Excel problems. I try to explore available options to guide readers to the maximum ways how they can solve. The world is limitless, and so are the problems in it. Let's help each other to grow. I would love to hear feedback and queries from readers. You can visit my LinkedIn for further information.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo