# Excel Formula: Compare Two Columns and Return a Value

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.

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

Approach

### 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.
=IF(B6=C6,โMatchโ,โNo Matchโ)

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

Approach

### 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.
=IF(EXACT(B6,C6),โMatchโ,โNo Matchโ)

• 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

Approach

### ย 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.
=IF(ISNA(MATCH(C6,\$B\$6:\$B\$12,0)),C6,โโ)

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

Approach

### ย 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.
=VLOOKUP(E6,B6:C12,2,TRUE)

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

Approach

### ย 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.
=INDEX(C6:C12,MATCH(E6,B6:B12,0))

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

๐ย  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

(Visited 40 times, 1 visits today)

#### Sakibul Hasan Nahid

Hello I am Sakibul Hasan Nahid. I have completed my BSc. in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations.

We will be happy to hear your thoughts