# How to Find Same Values in Two Columns in Excel Using a Formula?

When working with large datasets in **Excel**, it’s common to need to **compare two columns** to find matching values. This can be particularly useful in scenarios like identifying duplicates, validating data, or performing **cross-referencing** between lists. Fortunately, **Excel formulas** provide efficient ways to achieve this.

In this article, we will cover how to use Excel formulas to **find same values in two columns**, ensuring accuracy and saving time. We will explore different approaches using **built-in functions** like **IF**, **VLOOKUP**, **MATCH**, and **COUNTIF**.

## Why Finding Matching Values in Two Columns is Important

Before we dive into the actual formulas, let’s first understand why this task is critical:

**Data Validation**: Ensuring that information from two datasets matches is key in verifying accuracy.**Removing Duplicates**: You may need to identify and eliminate duplicate entries.**Data Comparison**: When combining data from different sources, finding matches ensures you’re not missing key information.

Let’s now get into the practical ways to find matching values in Excel.

## Comparing Two Columns Using the IF Formula

One of the most straightforward ways to **find same values** in two columns is by using the **IF function**. The **IF formula** evaluates whether a condition is true or false, and you can use it to compare two cells.

### Syntax of the IF Function

`=IF(condition, value_if_true, value_if_false)`

In our case, the condition will be whether the value in **Column A** equals the value in **Column B**.

### Example

Assume you have data in **Column A** (A2:A10) and **Column B** (B2:B10), and you want to find out if the values in these two columns match.

- Select a new column (e.g.,
**Column C**). - Enter the following formula in
**C2**:

`=IF(A2=B2, "Match", "No Match")`

- Drag the formula down to compare all values in the range.

Column A | Column B | Column C |
---|---|---|

Apple | Apple | Match |

Banana | Orange | No Match |

Cherry | Cherry | Match |

Date | Banana | No Match |

This simple formula helps you easily see if the values in **two columns** are the same.

## Using the VLOOKUP Formula to Find Matches

The **VLOOKUP function** is one of Excel’s most popular tools for looking up values. It’s ideal for **finding matching values** between columns, especially when dealing with large datasets.

### Syntax of the VLOOKUP Function

`=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`

For this task, we will use **VLOOKUP** to search for a value from **Column A** in **Column B**.

### Example

Let’s say you have data in **Column A** and **Column B**, and you want to find if values from **Column A** exist in **Column B**.

- In
**Column C**, enter the following formula:

`=IF(NOT(ISNA(VLOOKUP(A2, B:B, 1, FALSE))), "Match", "No Match")`

Here’s what happens:

**VLOOKUP(A2, B:B, 1, FALSE)**looks for the value in A2 within Column B.**ISNA**checks if**VLOOKUP**returns an error (if the value isn’t found).**IF**displays “Match” if the value is found and “No Match” if not.

## Finding Same Values Using the COUNTIF Function

Another way to find matching values between two columns is by using the **COUNTIF function**. This function counts how many times a specific value appears in a given range.

### Syntax of the COUNTIF Function

`=COUNTIF(range, criteria)`

In this case, we’ll check how many times a value from **Column A** appears in **Column B**.

### Example

- Enter the following formula in
**C2**:

`=IF(COUNTIF(B:B, A2) > 0, "Match", "No Match")`

- Drag the formula down.

Column A | Column B | Column C |
---|---|---|

Apple | Apple | Match |

Banana | Orange | No Match |

Cherry | Cherry | Match |

Date | Banana | No Match |

This approach uses **COUNTIF** to count the occurrences of each value in **Column B**, providing an efficient method for comparison.

## Using the MATCH Function to Compare Two Columns

The **MATCH function** is also useful for comparing two columns in Excel. It returns the relative position of an item in a specified range.

### Syntax of the MATCH Function

`=MATCH(lookup_value, lookup_array, [match_type])`

To find out if values in **Column A** exist in **Column B**, we will use the **MATCH** function combined with **IF**.

### Example

- In
**C2**, enter this formula:

`=IF(ISNUMBER(MATCH(A2, B:B, 0)), "Match", "No Match")`

**MATCH(A2, B:B, 0)**looks for an exact match of the value in**A2**within**Column B**.**ISNUMBER**checks if**MATCH**finds a value, returning a number if true and an error if false.

2. Drag the formula down for all cells in **Column C**.

This method is particularly useful when working with larger datasets, as **MATCH** can handle complex lookups effectively.

## Highlight Matching Values in Two Columns Using Conditional Formatting

Here are the steps to **find and highlight matching values in two columns** using **Conditional Formatting** without bullet points:

**Select the First Column (e.g., Column A)**:

Highlight the range of cells in**Column A**where you want to find matching values. For example, select**A2:A10**.**Go to Conditional Formatting**:

In the Excel ribbon, go to the**Home**tab and click on**Conditional Formatting**under the**Styles**group.**Create a New Rule**:

From the dropdown, select**New Rule**.**Use a Formula to Determine Which Cells to Format**:

In the**New Formatting Rule**dialog, choose**Use a formula to determine which cells to format**.**Enter the Formula to Find Matching Values**:

In the formula input box, enter the following formula to compare values between**Column A**and**Column B**:

` =MATCH(A2, B:B, 0)`

This formula checks if each value in **Column A** appears anywhere in **Column B**. If the value is found, it returns a number indicating the row position in **Column B** where the match occurs.

**Set the Formatting Style**:

Click the**Format**button to choose how you want to highlight the matching values. You can select a color fill, bold text, or any other style to easily identify the matches. After choosing your format, click**OK**.**Apply the Rule**:

Click**OK**again to apply the rule and see the matching values highlighted in**Column A**.**Repeat for Column B (Optional)**:

If you want to highlight matches in**Column B**as well, repeat the process for**Column B**, but use this formula to compare values in**Column B**with**Column A**:

` =MATCH(B2, A:A, 0)`

Now, the matching values in both columns will be highlighted, allowing you to quickly identify duplicates or shared data across **Column A** and **Column B**.

### Example

Column A | Column B |
---|---|

Apple | Orange |

Banana | Banana |

Cherry | Cherry |

Date | Grape |

After applying **Conditional Formatting**, the cells containing “Banana” and “Cherry” in **Column A** will be highlighted because they are also found in **Column B**.

## Using Array Formulas to Find Matching Values

Array formulas allow you to perform complex calculations on multiple data sets simultaneously. One advanced method of comparing two columns for matching values is to use an **array formula**.

### Example

- Enter the following formula in
**C2**:

`=IF(OR(A2=B2:B10), "Match", "No Match")`

- Press
**Ctrl + Shift + Enter**to activate the array formula.

This will compare each value in **Column A** with all the values in **Column B** and return “Match” for any found duplicates.

## Combining INDEX and MATCH for Advanced Comparison

In more advanced scenarios, using a combination of **INDEX** and **MATCH** can give you more flexibility when comparing two columns. This approach allows you to find the position of matching values and return the corresponding result.

### Syntax of INDEX and MATCH

`=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))`

In this formula:

**MATCH**identifies the position of the matching value.**INDEX**returns the value from the specified range.

#### Example

If you want to return a corresponding value from another column based on a match between **Column A** and **Column B**, you can use the formula:

`=IF(ISNUMBER(MATCH(A2, B:B, 0)), INDEX(B:B, MATCH(A2, B:B, 0)), "No Match")`

This advanced technique can be useful when you need to retrieve associated data once a match is found.

## Common Mistakes to Avoid When Finding Matching Values

While working with Excel formulas to **find same values** in two columns, there are a few common mistakes you should avoid:

**Forgetting to Lock Cell References**: When using formulas like**VLOOKUP**or**COUNTIF**, it’s crucial to lock the reference range if you’re dragging the formula across multiple rows.**Mismatched Data Types**: Ensure both columns have the same data type (e.g., text, number) to avoid errors in matching.**Using Inconsistent Ranges**: Always double-check that your formulas are referring to the correct ranges to avoid missing matches.

## Final Thoughts

In Excel, finding **same values in two columns** can be easily done with a variety of **formulas** such as **IF**, **VLOOKUP**, **MATCH**, and **COUNTIF**. Each of these formulas offers its own advantages depending on the complexity of the data and the comparison you need to perform. Additionally, you can use tools like **Conditional Formatting** for quick visual comparisons, or **Array Formulas** and **INDEX-MATCH** for more advanced scenarios.

With these methods, you can ensure accuracy and efficiency when comparing columns in Excel, making your workflow smoother and more productive.

## FAQs

### How do I find matching values in two columns in Excel?

To find matching values between two columns in Excel, you can use various formulas like IF, VLOOKUP, MATCH, or COUNTIF. These functions help compare values from one column to another and return ‘Match’ or ‘No Match’ based on the condition.

### Can I use VLOOKUP to find matching values between two columns?

Yes, you can use the VLOOKUP function to find matching values between two columns. VLOOKUP searches for a value from one column in another column and returns ‘Match’ if the value is found.

### Can I compare two columns using the COUNTIF function?

Yes, the COUNTIF function is a simple way to compare two columns in Excel. COUNTIF checks how many times a value from one column appears in another and returns ‘Match’ if the count is greater than zero.

### How can I use the MATCH function to find duplicates in Excel?

You can use the MATCH function to compare values between two columns by checking if each value in one column appears in the other. Combine MATCH with IF or ISNUMBER to return ‘Match’ or ‘No Match’ for each comparison.

### What is the advantage of using INDEX and MATCH over VLOOKUP?

INDEX and MATCH provide more flexibility than VLOOKUP because they can look for matches in any direction, not just from left to right. Additionally, INDEX and MATCH can handle more complex lookups and return more precise results.

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.