Sakibul Hasan Nahid
We sometimes compare columns in Microsoft Excel. Microsoft Excel has a variety of functions for comparing and matching data. In this article, we will study five Excel formula to Compare Two Columns and Return a Value.
📁 Download Excel File
Download the Excel file below.
Learn to Compare Two Columns and Return a Value Using Excel Formula with These 5 Suitable Approaches
Here we will use the following dataset to compare two columns and return a value. The following dataset includes the assignments submitted by the students.
1. Using IF Function for Case-Insensitive Matches
In this method, we will use the IF function to compare two columns for case-insensitive matches and it will return “Match” or “Not Matched” comparing the two columns.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, go to the case-insensitive worksheet.
- After that, select cell D6.
- Then enter the following formula.
- Then press Enter key.
- Now select cell D6 and drag Fill Handle icon from D6 to D12.
Here we can see that in Status column returns Match if the name in Column B and Column C is the same irrespective of whether the first letter is capitalized or not.
Read More: 4 Ways to Compare Three Columns in Excel and Return a Value
2. Utilizing IF Function for Case-Sensitive Matches
In this method, we will use the IF function to compare two columns for case-sensitive matches and it will return “Match” or “Not Matched” comparing the two columns.
For that here we will also use the EXACT function. EXACT function compares two text strings and returns TRUE if they are identical; otherwise, it returns FALSE. The EXACT function is case-sensitive.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, go to Case-sensitive worksheet and select cell D6.
- Then enter the following formula.
- Then press Enter and as you can see here Thomas and thomas are the same names but in the second column the name starts with a small “t” so it should return No Match.
- Now, select cell D6 and drag the Fill Handle icon from D6 to D12.
Here, we can see that the names are identical, but based on the case of the letters, the function returns either Match or No Match.
Read More: 5 Ways to Compare 3 Columns for Matches in Excel
3. Combining MATCH, IF and ISNA Functions
In the third method we are going to use a combination of MATCH, IF and ISNA functions.
Here MATCH function identifies any matching item. If any match is found, the location within the text or text series is returned. The ISNA function is a type of IS function that checks the given value and, based on the result, returns TRUE or FALSE.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, go to the IF+MATCH+ISNA worksheet and select cell D6.
- Then enter the following formula.
- Now press Enter Here the names in assignment 2 match with the names of assignment 1 it will return an empty string and if it doesn’t match that name will return in the status column.
- Now select cell D6 and drag Fill Handle icon from D6 to D12.
We can see that the names Angela , Hector and Perry exists in the assignment 2 column but don’t exist in the assignment 1 column. So the formula returned these names in the Status column.
🔨 Formula Breakdown
IF(ISNA(MATCH(C6,$B$6:$B$12,0)),C6,””)
👉 (MATCH(C6,$B$6:$B$12,0) here the C6 is the lookup value. This value in C6 cell will be searched, $B$6:$B$12 in this given array. And 0 is used to indicate the match type.
👉 ISNA(MATCH(C6,$B$6:$B$12,0)) here ISNA function is utilized to check for #N/A errors in cells or calculations.
👉IF(ISNA(MATCH(C6,$B$6:$B$12,0)),C6,””) and finally the IF function means if the match is not found return the lookup value otherwise return an empty string.
Read More: 2 Ways to Compare 4 Columns Using VLOOKUP Function in Excel
4. Applying VLOOKUP Function
Now in the 4th method, we will use the VLOOKUP function. The VLOOKUP function takes four arguments. The value we want to look up, the array from where we want to lookup, the column number which contains the return value, and for exact match we use true otherwise false. Here in this dataset we want to lookup the student ids in Column E and extract their name from the table.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, go to the VLOOKUP worksheet, and select cell F6.
- Then enter the given formula.
- After that, press the Enter key.
- Now select cell F6 and drag Fill Handle icon from F6 to F8.
Here we can see that we have extracted the names from the table against the Student IDs provided in column E.
Read More: 5 Ways to Compare Three Columns Using Vlookup in Excel
5. Implementing INDEX and MATCH Functions
Here in the final method, we are going to use the combination of the INDEX and MATCH functions to compare two columns and return a value. Here MATCH function finds a range of cells for a specified item and returns the item’s relative location inside the range. The return value from the MATCH function then passed into the INDEX function to return the desired value from the array. Here in this method, we will use this combination to extract values from a given array.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, go to the INDEX-MATCH worksheet and select cell F6.
- Secondly, enter the given formula.
- Then press the Enter key.
- Select cell F6 and drag Fill Handle icon from F6 to F8.
And we can see that we have extracted our desired student names against their student ids from the given array.
🔨 Formula Breakdown
INDEX(C6:C12,MATCH(E6,B6:B12,0))
👉Here MATCH(E6,B6:B12,0) looks for a match of the value present in E6 cell from the B6:B12 array and returns a number. In this function it returns 3 for 1612003.
👉 Then INDEX(C6:C12,MATCH(E6,B6:B12,0)) here the INDEX function takes 3 as the row number ar search for the value in C6:C12 range and returns the name Sergino.
📝 Takeaways from This Article
📌 In this article we learned five excel formulas to compare two columns and return a value.
📌 We also learned the use of various excel functions.
📌 In the first method we used IF function but in the second method for case-sensitive matches, we used the combination of IF and EXACT functions.
📌 Later we used a combination of IF, MATCH, and ISNA functions.
📌 Then we used the VLOOKUP function and in the final method, we used INDEX-MATCH function.
Conclusion
We have reached the conclusion of this article. In this post, we have attempted to present every excel formula to compare two columns and return a value. As several approaches have been outlined in this article, you may easily choose which one is appropriate for you. Please post any queries in the comments section below. Finally, we recommend visiting Excelden to learn more about Excel.
Related Articles
- 4 Ways to Compare Two Columns in Excel for Missing Values
- How to Count Matches in Two Columns in Excel
