Farihat
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.
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.
- Third, hit the Enter key.
- Fourth, copy the formulas down their cells using the Fill Handle icon.
- Fifth, you will find whether all three columns have the same values.
🔨 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
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.
- Then, hit the Enter key.
- 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.
- Next, hit the Enter key.
- 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.
- Then, hit the Enter key.
- 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
- 4 Ways to Compare Two Columns in Excel for Missing Values
- How to Count Matches in Two Columns in Excel
