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.

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

**ย Read More: ****4 Ways to Compare Three Columns in Excel and Return a Value**

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

**ย Read More: ****2 Ways to Compare 4 Columns Using VLOOKUP Function in Excel**

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

**ย Read More: ****5 Ways to Compare Three Columns Using Vlookup in Excel**

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

## ๐ 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**