Excel is one of the significant applications used in both computers and mobile. We cannot think of a modern corporate house that is not using Excel. Working with Excel means lots of data, columns, and rows. In this article, we discuss the 4 Examples formula to compare two columns and return a value in Excel with proper illustrations.

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

## 5 Methods to Excel Formula to Compare Two Columns and Return a Value

We will apply 4 different methods to **compare two columns and return a value**. We may need to use different data sets for different methods. Initially, we are taking data of some project managers’ names working on two projects.

### 1. IF-EXACT Functions to** Compare Two Columns and Return a Value**

**The IF function** allows us to make logical comparisons between a value and our desire. This **IF **statement can have two results. The first result is if our comparison is True, the second is if our comparison is False.

**Syntax:**

**IF(logical_test, value_if_true, [value_if_false])**

**Argument:**

** logical_test** – The desired condition we want to test.

** value_if_true** – The value we want to return if the result of logical_test is

**TRUE**.

** value_if_false** – The value will return if the result of logical_test is

**FALSE**.

#### 1.1 Case-insensitive Approach

This approach is not case-sensitive. That means **Jeo **and **jeo **are considered the same.

**Step 1:**

- Go to
**Cell D5**. - Write the following formula:

`=IF(B5=C5,"Match","No Match")`

**Step 2:**

- Then, press
**Enter**.

**Step 3:**

- Pull the
**Fill Handle**icon.

Now, in the status column, we see that **Match **for where both columns are the same otherwise **No Match**.

#### 1.2 Case-sensitive Approach

**The EXACT function** compares two text strings and returns **TRUE** if they are the same, otherwise shows **FALSE**. **EXACT **is case-sensitive but ignores formatting differences.

**Syntax:**

**EXACT(text1, text2)**

**Arguments:**

** text1** – It is the first text string.

** text2** – It is the second text string.

Here, we will use **the Exact Function**. Which gives a match result with case sensitivity.

**Step 1:**

- Go to
**Cell D5**like previous. - Modify the
**IF**function and**EXACT**function with this. So, the formula is:

`=IF(EXACT(B5,C5),"Match","No Match")`

**Step 2:**

- Press the
**Enter**button.

**Step 3:**

- Drag the
**Fill Handle**icon.

Here, we see that **No Match** is showing in the 10th row, because of case sensitivity.

**Read more:** **How to Compare Two Columns or Lists in Excel**

### 2. IF+ISNA+MATCH Functions to Return the Mismatched from 2nd Column

**The MATCH function** is used to find any matched object. If finds any match then returns the position from the text or text series.

**Syntax:**

**MATCH(lookup_value, lookup_array, [match_type])**

**Arguments**:

** lookup_value –** This object will be searched in the lookup_array. It may be any text, numeric value, etc.

** lookup_array –** This is our range where we will search for lookup_value.

** match_type –** It is optional. It defines the type of matching.

**The ISNA function** is a version of the **IS **function.

**Syntax:**

**ISNA(value)**

**Argument:**

** value – **Value refers to the

**#N/A**(value not available) error value.

**Step 1:**

- First, go to
**Cell D5.** - Then, write down the formula. The formula is:

`=IF(ISNA(MATCH(C5,$B$5:$B$12,0)),C5,"")`

**Step 2:**

- Now, press
**Enter**.

**Step 3:**

- Pull the
**Fill Handle**icon till the last cell with data.

In the status box, we see those names that are present only on **Project 2** not in **Project 1**.

**Similar Readings:**

**How to Compare Two Columns and Return Common Values in Excel****Match Two Columns in Excel and Return a Third (3 Ways)****Compare Two Columns in Excel for Match (8 ways)****How to Compare Two Columns in Excel For Finding Differences**

### 3. Formula with the VLOOKUP Function to Compare Two Columns

**The VLOOKUP function** is used when we need to find things in a table or a range regarding rows. In **VLOOKUP **we need to arrange data in a way so that it can check data towards the left. And provides a result after checking.

**Syntax:**

**VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])**

**Argument:**

** lookup_value –** This value will be checked. It stays in the initial column of the data range.

** table_array –** This is the range from where we will search the lookup_value.

** col_index_num –** This number defines from which column we need the return value.

** range_lookup –** This is optional. This argument defines the type of matching. Those are exact matching or approximate matching.

In this section, we will use the **VLOOKUP **function. Before that, we need to modify our data. After modification data will look like this.

**Step 1:**

- In the data set, we add two boxes for
**Project ID**and**Manager**. We will input the**Project ID**and get**Manager’s**name instantly.

**Step 2:**

- We put
**Project ID**A-003.

**Step 3:**

- Go to
**Cell F6.** - Write the
**VLOOKUP**So, the formula is:

`=VLOOKUP(E6,B5:C12,2,TRUE)`

**Step 4:**

- Then press
**Enter**.

We get the Manager’s name by inputting the **Project ID**.

**Step 5:**

We change the **Project ID** and press **Enter **to see the changes.

**Read more:** **VLOOKUP Formula to Compare Two Columns in Different Sheets!**

### 4. INDEX-MATCH Functions to **Compare Two Columns and Return a Value**

**The IFERROR function** is used to check errors. It can easily find any error after evaluating a value. If no error is found then give a proper value of the function.

**Syntax:**

**IFERROR(value, value_if_error)**

**Arguments:**

** value –** The given weight will be used to check to find the error.

**value_if_error –** This value will be shown if any error is found.

**The INDEX function** is used in two ways. If we want to return the value of a specified cell or array of cells, see Array form. Or see the reference form If we want to return a reference to specified cells.

**Syntax:**

**INDEX(array, row_num, [column_num])**

**Arguments:**

** array –** It is the range of cells or an array constant.

** row_num –** It is required unless column_num is present. It selects the row in an array from which to return a value. If row_num is omitted, column_num is required.

** column_num –** It elects the column in the array from which to return a value. If column_num is omitted, row_num is required.

**Step 1:**

- We took
**A-007**as the**Project ID**in**Cell E6**.

**Step 2:**

- Write the combination of
**INDEX**and**MATCH**The formula will be:

`=INDEX(C5:C11,MATCH(E6,B5:B12,0))`

**Step 3:**

- Then press
**Enter**.

We get the manager’s name according to the project id. If we give any input on the Project id box that is not present on our data set see what happens.

**Step 4:**

- We give an input
**A-010**and press**Enter**.

The result is showing **#N/A**. To avoid that values we will insert the **IFERROR **function.

**Step 5:**

- Add the
**IFERROR**function with the existing formula. So, the formula will be:

`=IFERROR(INDEX(C5:C11,MATCH(E6,B5:B12,0)),"")`

**Step 6:**

- Then, press
**Enter**.

Now, due to using the **IFERROR **function if the reference is not found on the data the result will be blank.

**Formula Breakdown:**

**MATCH(E6,B5:B12,0)**

This function searches a match of **Cell E6 **in the range **B5 **to **B12**.

**Output: 7**

**INDEX(C5:C11,MATCH(E6,B5:B12,0))**

This function searches the output of the **MATCH **function on the range **C5 **to **C11.**

**Output: Josef**

**IFERROR(INDEX(C5:C11,MATCH(E6,B5:B12,0)),””)**

This will return the value of **INDEX **if the value is valid, otherwise, the cell will show blank.

**Output: Josef**

**Return a Value from the Third Column**

In this section, we will compare two columns and get results from the third column. We modify the data set first.

**Step 1:**

- We will use
**Project ID**and**Manager**as references to compare and get the output from**Salary**.

**Step 2:**

- Give input on the reference boxes.

**Step 3:**

- Now, write the formula in
**Cell C16**. The formula is:

`=INDEX(D5:D12,MATCH(C14&C15,B5:B12&C5:C12,0))`

**Step 4:**

- Then, press
**Ctrl+Shift+Enter**as it is an array function.

Now, we see that result is from the third column.

## Conclusion

In this article, we showed Excel formula to compare two columns and return a value. I hope this will satisfy your needs. Please have a look at our website **Exceldemy.com** and give your suggestions in the comment box.